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

중복제거 및 우선순위 값 출력 쿼리

by OneQue [Oracle 기초] Oracle 중복제거 [2022.12.06 18:41:00]

형식이 같은 a데이터와 b데이터를 union all로 합친 뒤 중복제거를 하려고 하는데

중복인 경우 b데이터의 결과만이 남도록 쿼리를 짜고 싶습니다.

group by 나 ROW_NUMBER() 사용해서 짜보려고 했는데 잘 안돼서 질문 드립니다.

data a			data b			data result
colum1	colum2	colum3	colum1	colum2	colum3	colum1	colum2	colum3
한국	국가	5	한국	국가	3	한국	국가	3
미국	국가	1	일본	국가	5	미국	국가	1
일본	국가	1	영국	국가	3	일본	국가	5
중국	국가	2	프랑스	국가		중국	국가	2
						영국	국가	3
						프랑스	국가

SELECT * FROM (
WITH test1 AS (
	SELECT '한국' AS column1,'국가' AS column2,'5' AS column3 FROM dual UNION all 
	SELECT '미국' AS column1,'국가' AS column2,'1' AS column3 FROM dual UNION all 
	SELECT '일본' AS column1,'국가' AS column2,'1' AS column3 FROM dual UNION all 
	SELECT '중국' AS column1,'국가' AS column2,'2' AS column3 FROM dual
	), test2 AS (
	SELECT '한국' AS column1,'국가' AS column2,'3' AS column3 FROM dual UNION all 
	SELECT '미국' AS column1,'국가' AS column2,'5' AS column3 FROM dual UNION all 
	SELECT '일본' AS column1,'국가' AS column2,'3' AS column3 FROM dual UNION all 
	SELECT '중국' AS column1,'국가' AS column2,null AS column3 FROM dual
)
SELECT column1, column2, column3 FROM test1 UNION all 
SELECT column1, column2, column3 FROM test2);

by 마농 [2022.12.07 08:48:12]

WITH test1 AS
(
SELECT '한국' column1, '국가' column2, '5' column3 FROM dual
UNION all SELECT '미국', '국가', '1' FROM dual
UNION all SELECT '일본', '국가', '1' FROM dual
UNION all SELECT '중국', '국가', '2' FROM dual
)
, test2 AS
(
SELECT '한국' column1, '국가' column2, '3' column3 FROM dual
UNION all SELECT '일본'  , '국가', '5' FROM dual
UNION all SELECT '영국'  , '국가', '3' FROM dual
UNION all SELECT '프랑스', '국가', ''  FROM dual
)

-- 1. full outer join
SELECT NVL(a.column1, b.column1) column1
     , NVL(a.column2, b.column2) column2
     , NVL(a.column3, b.column3) column3
  FROM test2 a
  FULL OUTER JOIN test1 b
    ON a.column1 = b.column1
   AND a.column2 = b.column2
;

-- 2. 구분값 추가, KEEP
SELECT column1
     , column2
     , MIN(column3) KEEP(DENSE_RANK FIRST ORDER BY gb) column3
  FROM (SELECT 1 gb, column1, column2, column3 FROM test2
         UNION ALL
        SELECT 2 gb, column1, column2, column3 FROM test1
        )
 GROUP BY column1, column2
;

-- 3. 구분값 추가, ROW_NUMBER
SELECT column1, column2, column3
  FROM (SELECT column1, column2, column3
             , ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY gb) rn
          FROM (SELECT 1 gb, column1, column2, column3 FROM test2
                 UNION ALL
                SELECT 2 gb, column1, column2, column3 FROM test1
                )
        )
 WHERE rn = 1
;

-- 4. 컬럼 분리하여 처리
SELECT column1
     , column2
     , NVL(MIN(column3), MIN(column4)) column3
  FROM (SELECT column1, column2, column3, '' column4 FROM test2
         UNION ALL
        SELECT column1, column2, '', column3 FROM test1
        )
 GROUP BY column1, column2
;

-- 5. test1 단독인 것만 UNION
-- 5.1. OUTER JOIN & IS NULL
SELECT column1, column2, column3
  FROM test2
 UNION ALL
SELECT b.column1, b.column2, b.column3
  FROM test1 b
  LEFT OUTER JOIN test2 a
    ON a.column1 = b.column1
   AND a.column2 = b.column2
 WHERE a.column1 IS NULL
;
-- 5.2. NOT EXISTS
SELECT column1, column2, column3
  FROM test2
 UNION ALL
SELECT column1, column2, column3
  FROM test1 b
 WHERE NOT EXISTS (SELECT 1
                     FROM test2 a
                    WHERE a.column1 = b.column1
                      AND a.column2 = b.column2
                   )
;
-- 5.3. NOT IN
SELECT column1, column2, column3
  FROM test2
 UNION ALL
SELECT column1, column2, column3
  FROM test1 b
 WHERE (column1, column2) NOT IN (SELECT column1, column2 FROM test2)
;

by OneQue [2022.12.07 09:31:13]

감사합니다!

댓글등록

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

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

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

데이터베이스 질문과 답변