형식이 같은 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);
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) ;