|
하기와 같이 T,S테이블을 조인해 NEW1테이블과 같이 나타내려 합니다.
T테이블 데이터는 모두 보여주고 T테이블을 S테이블과 조인하여 A~G컬럼 그룹으로 묶어 'AGG' 값으로 F컬럼을 나타내어
H컬럼에 SUM값만 한줄 더 보여주려고 하는데 어떻게 해야할지 문의 드립니다.
무식하게 아래처럼 T테이블 과 T,S조인한것의 합계를 UNION ALL해서 보여주면 될거 같긴한데 쿼리가 지저분해지고 더 빠른 수행속도를 낼수 있는가 해서
문의 드립니다.
SELECT *
FROM (
SELECT T.A, T.B, T.C, T.D, T.E, t.F, T.G, t.h
FROM T
UNION ALL
SELECT T.A, T.B, T.C, T.D, T.E, 'AGG' F, T.G, SUM(T.H)
FROM T, S
WHERE T.A = S.A
AND T.B = S.B
AND T.C = S.C
AND T.D = S.D
AND T.E = S.E
AND T.F = S.F
AND T.G = S.G
GROUP BY T.A, T.B, T.C, T.D, T.E, T.F, T.G--ROLLUP(T.A, T.B, T.C, T.D, T.E, T.F, T.G)
) A
WITH t AS
(
SELECT 'C4' A, '373' B, 'TEST' C, 'A01' D, '20140713' E, 'OUT' F, 'K-1' G, 1072 H FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A01', '20140713', 'OUT', 'K-1', 500 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A01', '20140713', 'OUT', 'E' ,1100 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'K-1', 581 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'E' ,815 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'K-1', 54 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'K-1', 305 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'K-1', 900 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'E' ,825 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A04', '20140720', 'OUT', 'E' ,1088 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A05', '20140721', 'OUT', 'E' ,453 FROM dual
)
SELECT *
FROM T
WITH S AS
(
SELECT 'C4' A,'373' B, 'TEST' C, 'A01' D, '20140713' E, 'OUT' F, 'K-1' G FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'E' FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'K-1' FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A04', '20140720', 'OUT', 'E' FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A05', '20140721', 'OUT', 'E' FROM dual
)
SELECT *
FROM S
WITH NEW1 AS
(
SELECT 'C4' A, '373' B, 'TEST' C, 'A01' D, '20140713' E, 'AGG' F, 'K-1' G, 1572 H FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A01', '20140713', 'OUT', 'K-1', 1072 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A01', '20140713', 'OUT', 'K-1', 500 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A01', '20140713', 'OUT', 'E' , 1100 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'AGG', 'E', 815 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'K-1', 581 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'E' , 815 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A02', '20140713', 'OUT', 'K-1', 54 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'AGG', 'K-1', 1205 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'K-1', 305 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'K-1', 900 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A03', '20140717', 'OUT', 'E' , 825 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A04', '20140720', 'AGG', 'E' , 1088 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A04', '20140720', 'OUT', 'E' , 1088 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A05', '20140721', 'AGG', 'E' , 453 FROM dual
UNION ALL SELECT 'C4', '373', 'TEST', 'A05', '20140721', 'OUT', 'E' , 453 FROM dual
)
SELECT *
FROM NEW1 ;
감사합니다.
|
