데이터베이스 질문과 답변
2개의 테이블이 있는데 하나는 코드 리스트 테이블로 이러한 모양이고
|
코드 |
상위코드 |
LEVEL |
EDIT_YN |
|
A |
1 |
N |
|
|
A1 |
A |
2 |
N |
|
A1-1 |
A1 |
3 |
Y |
|
A1-2 |
A1 |
3 |
Y |
|
A2 |
A |
2 |
Y |
|
B |
1 |
N |
|
|
B1 |
B |
2 |
Y |
|
B2 |
B |
2 |
Y |
다른 테이블은 이렇게 데이터가 있는 테이블입니다.
|
코드 |
구분 |
값 |
|
A1-1 |
가 |
2 |
|
A1-1 |
나 |
3 |
|
A1-1 |
다 |
2 |
|
A1-2 |
가 |
4 |
|
A1-2 |
나 |
4 |
|
A1-2 |
다 |
1 |
|
A2 |
가 |
2 |
|
B1 |
가 |
5 |
|
B1 |
다 |
1 |
|
B2 |
나 |
3 |
|
B2 |
다 |
2 |
(코드 리스트의 EDIT_YN의 값이 Y인 것의 데이터만 존재)
두 테이블을 이용하여 아래 모양의 결과를 MSSQL(2010)로 가져오고싶습니다.
|
코드 |
합 |
가 |
나 |
다 |
|
A |
18 |
8 |
7 |
3 |
|
A1 |
16 |
6 |
7 |
3 |
|
A1-1 |
7 |
2 |
3 |
2 |
|
A1-2 |
9 |
4 |
4 |
1 |
|
A2 |
2 |
2 |
0 |
0 |
|
B |
11 |
5 |
3 |
3 |
|
B1 |
6 |
5 |
0 |
1 |
|
B2 |
5 |
0 |
3 |
2 |
3레벨의 합으로 2레벨 합을 구하고 그 2레벨들의 합으로 1레벨의 값을 구하려는데
MSSQL로는 어떻게 해야할지 알수있을까요?
by 마농
[2015.09.04 11:34:46]
WITH code AS
( -- 코드 테이블 --
SELECT 'A' cd, null pcd, 1 lv, 'N' yn
UNION ALL SELECT 'A1' , 'A' , 2, 'N'
UNION ALL SELECT 'A1-1', 'A1', 3, 'Y'
UNION ALL SELECT 'A1-2', 'A1', 3, 'Y'
UNION ALL SELECT 'A2' , 'A' , 2, 'Y'
UNION ALL SELECT 'B' , null, 1, 'N'
UNION ALL SELECT 'B1' , 'B' , 2, 'Y'
UNION ALL SELECT 'B2' , 'B' , 2, 'Y'
)
, data AS
( -- 데이터 테이블 --
SELECT 'A1-1' cd, '가' gb, 2 v
UNION ALL SELECT 'A1-1', '나', 3
UNION ALL SELECT 'A1-1', '다', 2
UNION ALL SELECT 'A1-2', '가', 4
UNION ALL SELECT 'A1-2', '나', 4
UNION ALL SELECT 'A1-2', '다', 1
UNION ALL SELECT 'A2' , '가', 2
UNION ALL SELECT 'B1' , '가', 5
UNION ALL SELECT 'B1' , '다', 1
UNION ALL SELECT 'B2' , '나', 3
UNION ALL SELECT 'B2' , '다', 2
)
, tmp0 AS
( -- 코드테이블 계층구조 생성 --
SELECT c.cd, c.pcd, c.lv
, CAST(c.cd AS VARCHAR(100)) p
, REPLICATE(' ', c.lv-1) + c.cd AS cds
FROM code c
WHERE pcd IS NULL
UNION ALL
SELECT c.cd, c.pcd, c.lv
, CAST(p.p + '/' + c.cd AS VARCHAR(100)) p
, REPLICATE(' ', c.lv-1) + c.cd AS cds
FROM tmp0 p
INNER JOIN code c
ON p.cd = c.pcd
)
, tmp1 AS
( -- 코드별 집계 --
SELECT c.cd, c.pcd, c.lv
, SUM(d.v) 합
, ISNULL(SUM(CASE d.gb WHEN '가' THEN v END), 0) 가
, ISNULL(SUM(CASE d.gb WHEN '나' THEN v END), 0) 나
, ISNULL(SUM(CASE d.gb WHEN '다' THEN v END), 0) 다
FROM code c
LEFT OUTER JOIN data d
ON c.cd = d.cd
GROUP BY c.cd, c.pcd, c.lv
)
, tmp2 AS
( -- 각 코드별 하위내역 계층전개 --
SELECT c.cd r_cd
, c.cd, c.pcd, c.lv
, c.합, c.가, c.나, c.다
FROM tmp1 c
UNION ALL
SELECT p.r_cd
, c.cd, c.pcd, c.lv
, c.합, c.가, c.나, c.다
FROM tmp2 p
INNER JOIN tmp1 c
ON p.cd = c.pcd
)
SELECT c.cds
, 합
, 가
, 나
, 다
FROM tmp0 c
INNER JOIN
( -- 코드별 하위내역 집계 --
SELECT r_cd
, SUM(합) 합
, SUM(가) 가
, SUM(나) 나
, SUM(다) 다
FROM tmp2
GROUP BY r_cd
) d
ON c.cd = d.r_cd
ORDER BY p
;
by raksasa
[2015.09.04 12:53:14]
감사합니다 ㅠ.ㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.