|
☞ 1. 직원별로 급여집계 |
| |
WITH salary AS
(
SELECT '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
SELECT '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
SELECT '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual
)
SELECT NAME, SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY NAME;
NAME SUM(SALARY)
-------- ------------
구하라 870000
저미주 327000
|
|
☞ 2. 직원별로 급여집계와 총계 구하기 |
| |
WITH salary AS
(
SELECT '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
SELECT '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
SELECT '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
SELECT '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual
)
SELECT NAME, SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(NAME);
NAME SUM(SALARY)
-------- ------------
구하라 870000
저미주 327000
327000 <-- ROLLUP을 사용한 결과 NAME항목 전체 대한 총계가 나 왔음
|
|
☞ 3. 부서별, 직원별 급여집계, 급여개월수 총계 구하기 |
| |
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, '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
)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY DEPT, NAME;
DEPT NAME COUNT(*) SUM(SALARY)
------------------------------------------
인사부 구하라 4 870000
인사부 이주리 3 480000
총무부 강모리 3 1570000
총무부 저미주 3 327000
|
| ☞ |
| |
4. 위 DATA를 가지고 아래와 같은 결과를 얻는 SQL문
DEPT NAME COUNT(*) SUM(SALARY)
------------------------------------------
인사부 구하라 4 870000 <--①
인사부 이주리 3 480000
인사부 7 1350000 <--②
총무부 강모리 3 1570000
총무부 저미주 3 327000
총무부 6 1897000
13 3247000 <--③
① ROLLUP(DEPT, NAME)한 결과 DEPT, NAME에 대한 합계 조회
② ROLLUP(DEPT, NAME)한 결과 DEPT 에 대한 합계 조회
③ ROLLUP(DEPT, 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, '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
)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME); |
|
☞ 정리 |
|
|
-. 정의 : 1.그룹핑된 결과에 그룹별합계정보를 추가하여 조회한다.
2. ROLLUP함수는 주로 GROUP BY와 같이 사용되며 주어진 Grouping조건에 따라 각 그룹의
그룹핑 항목이 있으면 우측부터 하나씩 제외하면서 그 결과를 반환한다.
-. ROLLUP(DEPT, NAME)을 하게 되면
1. 먼저 , GROUP BY DEPT, NAME 과 같은 결과 가 나오고
2. 그 다음에 , ROLLUP(DEPT, NAME)에 의해서 DEPT 에 대한 합계 조회
3. 마지막으로, ROLLUP(DEPT, NAME)에 의해서 총계가 조회 됨 | |
