-- 방법1. WITH T(C1, C2, C3, GRP, RNK) AS (SELECT 1, 2, 4, 1, 1 FROM DUAL UNION ALL SELECT 1, 2, 5, 1, 2 FROM DUAL UNION ALL SELECT 1, 2, 4, 1, 3 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 1 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 2 FROM DUAL) SELECT * FROM ( SELECT C1 , C2 , C3 , GRP , RNK , MIN(C1) KEEP (DENSE_RANK FIRST ORDER BY RNK) OVER (PARTITION BY GRP) C1_FIRST , MIN(C2) KEEP (DENSE_RANK FIRST ORDER BY RNK) OVER (PARTITION BY GRP) C2_FIRST , MIN(C3) KEEP (DENSE_RANK FIRST ORDER BY RNK) OVER (PARTITION BY GRP) C3_FIRST FROM T ) WHERE C1 != C1_FIRST OR C2 != C2_FIRST OR C3 != C3_FIRST; -- 방법2 WITH T(C1, C2, C3, GRP, RNK) AS (SELECT 1, 2, 4, 1, 1 FROM DUAL UNION ALL SELECT 1, 2, 5, 1, 2 FROM DUAL UNION ALL SELECT 1, 2, 4, 1, 3 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 1 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 2 FROM DUAL) SELECT T.* FROM T , ( SELECT * FROM T WHERE RNK = 1 ) T2 WHERE T.GRP = T2.GRP AND T.RNK != 1 AND ( T.C1 != T2.C1 OR T.C2 != T2.C2 OR T.C3 != T2.C3);
WITH t AS ( SELECT 1 c1, 2 c2, 4 c3, 1 grp, 1 rnk FROM dual UNION ALL SELECT 1, 2, 5, 1, 2 FROM dual UNION ALL SELECT 1, 2, 4, 1, 3 FROM dual UNION ALL SELECT 2, 3, 7, 2, 1 FROM dual UNION ALL SELECT 2, 3, 7, 2, 2 FROM dual ) -- 1. 집합 함수 : MINUS -- SELECT c1, c2, c3, grp FROM t WHERE rnk > 1 MINUS SELECT c1, c2, c3, grp FROM t WHERE rnk = 1 ; -- 2.1. Self Join -- SELECT * FROM t a , t b WHERE a.grp = b.grp AND a.rnk = 1 AND b.rnk > 1 AND ( a.c1 != b.c1 OR a.c2 != b.c2 OR a.c3 != b.c3 ) ; -- 2.2. Self Join Outer -- SELECT b.* FROM t a RIGHT OUTER JOIN t b ON a.grp = b.grp AND a.rnk = 1 AND a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 WHERE b.rnk > 1 AND a.rnk IS NULL ; -- 3. 계층 쿼리 -- SELECT * FROM t a WHERE LEVEL = 2 START WITH rnk = 1 CONNECT BY PRIOR grp = grp AND rnk > 1 AND ( PRIOR c1 != c1 OR PRIOR c2 != c2 OR PRIOR c3 != c3 ) AND LEVEL <= 2 ; -- 4. 분석함수 : FIRST_VALUE -- SELECT * FROM (SELECT c1, c2, c3, grp, rnk , FIRST_VALUE(c1) OVER(PARTITION BY grp ORDER BY rnk) c1_1 , FIRST_VALUE(c2) OVER(PARTITION BY grp ORDER BY rnk) c2_1 , FIRST_VALUE(c3) OVER(PARTITION BY grp ORDER BY rnk) c3_1 FROM t ) WHERE ( c1 != c1_1 OR c2 != c2_1 OR c3 != c3_1 ) ; -- 5.1. 서브 쿼리 : NOT IN -- SELECT * FROM t WHERE rnk > 1 AND (grp, c1, c2, c3) NOT IN (SELECT grp, c1, c2, c3 FROM t WHERE rnk = 1 ) ; -- 5.2. 서브 쿼리 : NOT EXISTS -- SELECT * FROM t a WHERE rnk > 1 AND NOT EXISTS (SELECT 1 FROM t WHERE rnk = 1 AND grp = a.grp AND c1 = a.c1 AND c2 = a.c2 AND c3 = a.c3 ) ;
wITH T(C1, C2, C3, GRP, RNK) AS (SELECT 1, 2, 4, 1, 1 FROM DUAL UNION ALL SELECT 1, 2, 5, 1, 2 FROM DUAL UNION ALL SELECT 1, 2, 4, 1, 3 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 1 FROM DUAL UNION ALL SELECT 2, 3, 7, 2, 2 FROM DUAL) SELECT * FROM ( SELECT t.*, MIN(c1||','||c2||','||c3) OVER(PARTITION BY grp ORDER BY rnk) AS rank_1 FROM t ) WHERE c1||','||c2||','||c3 <> rank_1
위 댓글들을 참조하여 변형하여 사용했습니다. -_-;;