|
|
--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), GROUPING(DEPT), GROUPING(NAME)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY CUBE(DEPT, NAME)
ORDER BY DEPT, NAME;
DEPT NAME COUNT(*) SUM(SALARY) GROUPING(DEPT) GROUPING(NAME)
----------------------------------------------------------------------------------
인사부 구하라 4 870000 0 0
인사부 이주리 3 480000 0 0
인사부 저미주 4 448000 0 0
인사부 11 1798000 0 1 <--CUBE에의한 결과
총무부 강모리 3 1570000 0 0
총무부 구하라 4 840000 0 0
총무부 저미주 3 327000 0 0
총무부 10 2737000 0 1 <--CUBE에의한 결과
강모리 3 1570000 1 0 <--CUBE에 의한결과
구하라 8 1710000 1 0 <--CUBE에 의한결과
이주리 3 480000 1 0 <--CUBE에 의한결과
저미주 7 775000 1 0 <--CUBE에 의한결과
21 4535000 1 1 <--CUBE에의한 결과
* 위 결과중 맨 아래자료는 (부서, 이름에 대한 총계 이므로 1,1의 결과가 나왔음)
SQL2 : 기타 추가 SQL문들
WITH는 위자료를 활용
SELECT DEPT, NAME, COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY DEPT, CUBE(NAME) --GROUP BY CUBE(DEPT), NAME
ORDER BY DEPT, NAME;
|
