-- 답에만 맞추었습니다. WITH T AS ( SELECT 'A' col1, '001' col2 FROM DUAL UNION ALL SELECT 'A' col1, '003' col2 FROM DUAL UNION ALL SELECT 'B' col1, '001' col2 FROM DUAL UNION ALL SELECT 'B' col1, '002' col2 FROM DUAL UNION ALL SELECT 'C' col1, '001' col2 FROM DUAL UNION ALL SELECT 'C' col1, '003' col2 FROM DUAL UNION ALL SELECT 'C' col1, '004' col2 FROM DUAL UNION ALL SELECT 'D' col1, '001' col2 FROM DUAL UNION ALL SELECT 'D' col1, '001' col2 FROM DUAL UNION ALL SELECT 'D' col1, '005' col2 FROM DUAL UNION ALL SELECT 'D' col1, '005' col2 FROM DUAL ) SELECT * FROM T WHERE col1 IN ( SELECT col1 FROM T GROUP BY col1 HAVING COUNT(DISTINCT DECODE(col2,'001',col2,'003',col2)) = 2 ) ORDER BY col1 , col2
WITH t AS ( SELECT 'A' col1, '001' col2 FROM dual UNION ALL SELECT 'A', '003' FROM dual UNION ALL SELECT 'B', '001' FROM dual UNION ALL SELECT 'B', '002' FROM dual UNION ALL SELECT 'C', '001' FROM dual UNION ALL SELECT 'C', '003' FROM dual UNION ALL SELECT 'C', '004' FROM dual UNION ALL SELECT 'D', '001' FROM dual UNION ALL SELECT 'D', '002' FROM dual UNION ALL SELECT 'D', '004' FROM dual UNION ALL SELECT 'D', '005' FROM dual ) SELECT col1, col2 FROM (SELECT col1, col2 , COUNT(CASE WHEN col2 IN ('001','003') THEN col2 END) OVER(PARTITION BY col1) cnt FROM t ) WHERE cnt = 2 ;