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

출처 : http://blog.naver.com/orapybubu/40025300615

 

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;