|
☞ 정의
|
| |
1. DATA가 아래와 같을 경우,
부서별총계, 직원별총계, 부서별,직원별 총계 를 구하기
[아래 자료를 보면 직원은 여러부서로 이동가능 하므로..
아래와 같은 자료가 존재 할 수 있다]
* 만약, 이 ROLLUP으로 하게 된다면, ROLLUP을 2개 사용후 UNION 을 해야 한다.
아래와 같은 결과가 나오기 위해서 SQL 문을 구현 (총3가지의 SQL 문으로 해 봤음) |
|
☞ 결과 |
| |
DEPT NAME COUNT(*) SUM(SALARY)
---------------------------------------------
인사부 구하라 4 870000
인사부 이주리 3 480000
인사부 저미주 4 448000
인사부 11 1798000
총무부 강모리 3 1570000
총무부 구하라 4 840000
총무부 저미주 3 327000
총무부 10 2737000
21 4535000 <--위에서 여기까는 첫번재 ROLLUP을이용한 결과
강모리 3 1570000
구하라 8 1710000
이주리 3 480000
저미주 7 775000
21 4535000 <-- 여기까지는 두번재 ROLLUP을 이용한결과
** 위 결과를 보면 총계가 2개나왔다. CUBE를 사용하게 되면 자연스럽게 1개만 나오게 된다
|
|
☞ SQL1 |
| |
WITH salary AS
(
SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME)
UNION ALL
SELECT '', NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(NAME) ; |
| ☞ SQL2 |
| |
-- UNION 만 했을 때의 결과 (data는 위 with절로 처리)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME)
UNION
SELECT '', NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(NAME) ; |
|
☞ SQL3
|
| |
-- 아래쪽 SQL 문을 GROUP BY 로만 했을 때의 결과 (data는 위 with절로 처리)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME)
UNION ALL
SELECT '', NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY NAME ;
WITH salary AS
(
SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY CUBE(DEPT, NAME)
ORDER BY DEPT, NAME ; | |
