UNION,GROUP BY : M:M 및 양쪽 Outer join을 자연스럽게 해결

출처 : http://blog.naver.com/orapybubu?Redirect=Log&logNo=40025300615

 

# UNION,GROUP BY : M:M 및 양쪽 Outer join을 자연스럽게 해결

 

create table IN_TAB
(CODE varchar2(4),
 IN_DATE varchar2(8),
 AMOUNT number);

 

insert into IN_TAB values(1000, '20030101', 1000);
insert into IN_TAB values(1000, '20030101', 6000);
insert into IN_TAB values(1000, '20030103', 2000);
insert into IN_TAB values(1000, '20030104', 7000);

create table OUT_TAB
(CODE varchar2(4),
 OUT_DATE varchar2(8),
 AMOUNT number);

 

insert into OUT_TAB values(1000, '20030102', 700);
insert into OUT_TAB values(1000, '20030102', 600);
insert into OUT_TAB values(1000, '20030103', 200);
insert into OUT_TAB values(1000, '20030104', 700);

 

SELECT CODE, IN_DATE AS DAY, AMOUNT AS IN_AMT, 0 AS OUT_AMT
FROM IN_TAB
UNION ALL
SELECT CODE, OUT_DATE, 0, AMOUNT
FROM OUT_TAB;

 

CODE	DAY	IN_AMT	OUT_AMT
1000	20030101	1000	0
1000	20030101	6000	0
1000	20030103	2000	0
1000	20030104	7000	0
1000	20030102	0	700
1000	20030102	0	600
1000	20030103	0	200
1000	20030104	0	700

 

SELECT CODE, DAY, 
       SUM(IN_AMT) AS IN_AMT,
       SUM(OUT_AMT) AS OUT_AMT
FROM (SELECT CODE, IN_DATE AS DAY, AMOUNT AS IN_AMT, 0 AS OUT_AMT
      FROM IN_TAB
      UNION ALL
      SELECT CODE, OUT_DATE, 0, AMOUNT
      FROM OUT_TAB)
GROUP BY CODE, DAY;

 

CODE	DAY	IN_AMT	OUT_AMT
1000	20030101	7000	0
1000	20030102	0	1300
1000	20030103	2000	200
1000	20030104	7000	700

 

-- 조금 더 개선

 

SELECT CODE, IN_DATE AS DAY, AMOUNT AS IN_AMT, TO_NUMBER(NULL) AS OUT_AMT
FROM IN_TAB
UNION ALL
SELECT CODE, OUT_DATE, TO_NUMBER(NULL), AMOUNT
FROM OUT_TAB;

 

SELECT CODE, DAY, 
       SUM(IN_AMT) AS IN_AMT,
       SUM(OUT_AMT) AS OUT_AMT
FROM (SELECT CODE, IN_DATE AS DAY, AMOUNT AS IN_AMT, TO_NUMBER(NULL) AS OUT_AMT
      FROM IN_TAB
      UNION ALL
      SELECT CODE, OUT_DATE, TO_NUMBER(NULL), AMOUNT
      FROM OUT_TAB)
GROUP BY CODE, DAY;

 

SELECT CODE, DAY, 
       NVL(SUM(IN_AMT), 0) AS IN_AMT,
       NVL(SUM(OUT_AMT), 0) AS OUT_AMT
FROM (SELECT CODE, IN_DATE AS DAY, AMOUNT AS IN_AMT, TO_NUMBER(NULL) AS OUT_AMT
      FROM IN_TAB
      UNION ALL
      SELECT CODE, OUT_DATE, TO_NUMBER(NULL), AMOUNT
      FROM OUT_TAB)
GROUP BY CODE, DAY;

 

 

---------------------------------------------------------------------------------------------------------------------------

 

 

WITH a AS

(

 SELECT '1' id FROM DUAL UNION ALL

 SELECT '2' id FROM DUAL UNION ALL

 SELECT '3' id FROM DUAL UNION ALL

 SELECT '4' id FROM DUAL

)

, b AS

(

 SELECT '2' id FROM DUAL UNION ALL

 SELECT '3' id FROM DUAL UNION ALL

 SELECT '5' id FROM DUAL UNION ALL

 SELECT '6' id FROM DUAL

)

SELECT MAX( CASE WHEN CHK = 'a' THEN ID END ) AS c1

 , MAX( CASE WHEN CHK = 'b' THEN ID END ) AS c2

  FROM ( SELECT 'a' AS chk

    , id

     from a

  UNION ALL

   SELECT 'b' AS chk

    , id

     from b

  )

 GROUP BY ID

 ORDER BY 1,2

[출처] UNION,GROUP BY : M:M 및 양쪽 Outer join을 자연스럽게 해결|작성자 마루아라