꿈꾸는 개발자, DBA 커뮤니티 구루비

교집합 질문입니다.

by 한결 [2017.08.17 16:46:14]

데이터중 교집합인 부분만 조회하고자 합니다.

WITH T AS (
SELECT 1 AS ID, 'A' AS NM FROM DUAL UNION ALL
SELECT 2 AS ID, 'A' AS NM FROM DUAL UNION ALL
SELECT 3 AS ID, 'A' AS NM FROM DUAL UNION ALL
SELECT 1 AS ID, 'B' AS NM FROM DUAL UNION ALL
SELECT 2 AS ID, 'B' AS NM FROM DUAL UNION ALL
SELECT 3 AS ID, 'B' AS NM FROM DUAL UNION ALL
SELECT 1 AS ID, 'C' AS NM FROM DUAL UNION ALL
SELECT 2 AS ID, 'C' AS NM FROM DUAL UNION ALL
SELECT 1 AS ID, 'D' AS NM FROM DUAL
)
SELECT * FROM T
WHERE T.NM IN ('A', 'C', 'D');

NM 은 조회조건으로 넘어와 변동됩니다.

INTERSECT 로 교집합만 구하려고 하는데,

이런식으로 하면 원하는 결과값은 나오지만

조회조건마다

INTERSECT
SELECT ID FROM T
WHERE T.NM = '' 이부분을 계속 추가할수는 없습니다.

다른 방법이 있을련지요..

by 우리집아찌 [2017.08.17 17:00:16]

WITH T AS (
  SELECT 1 AS ID, 'A' AS NM FROM DUAL UNION ALL
  SELECT 2 AS ID, 'A' AS NM FROM DUAL UNION ALL
  SELECT 3 AS ID, 'A' AS NM FROM DUAL UNION ALL
  SELECT 1 AS ID, 'B' AS NM FROM DUAL UNION ALL
  SELECT 2 AS ID, 'B' AS NM FROM DUAL UNION ALL
  SELECT 3 AS ID, 'B' AS NM FROM DUAL UNION ALL
  SELECT 1 AS ID, 'C' AS NM FROM DUAL UNION ALL
  SELECT 2 AS ID, 'C' AS NM FROM DUAL UNION ALL
  SELECT 1 AS ID, 'D' AS NM FROM DUAL
)
SELECT ID , VAL
 FROM  (SELECT ID , LISTAGG(NM,'|') WITHIN GROUP(ORDER BY NM ) VAL
          FROM T
         GROUP BY ID )
 WHERE VAL LIKE '%A%C%D%'

 --REGEXP_COUNT 이용
SELECT ID , VAL 
 FROM  (SELECT ID , LISTAGG(NM,'|') WITHIN GROUP(ORDER BY NM ) VAL
          FROM T
         GROUP BY ID )
 WHERE REGEXP_COUNT(VAL,'A|C|D') >= 3 

-- 그냥 간단했네요..
SELECT ID 
  FROM T 
 WHERE NM IN ( 'A' ,'C' ,'D' )
 GROUP BY ID
 HAVING COUNT(DISTINCT NM ) >= 3

by 한결 [2017.08.17 17:12:50]

답변 감사합니다.

그런데, IN 조건에 최대 200 개 정도가 넘어올 수 있습니다..

by 마농 [2017.08.17 17:17:20]

SELECT id
  FROM t
 WHERE nm IN ('A', 'C', 'D')
 GROUP BY id
 HAVING COUNT(*) = 3
;

by 한결 [2017.08.17 17:32:25]

두분 답변감사합니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변