| |
1. partition by와 함께 사용하여 부서를 한번만 인쇄하기
소계, 합계 명칭 보여주기
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 DECODE(ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY NAME), 1, DEPT, '')DEPT
, DECODE(GROUPING(NAME), 1, (DECODE(GROUPING(DEPT), 1, '총계', '소계')), NAME)NAME
, COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME)
-- GROUP BY CUBE(DEPT, NAME)
HAVING GROUPING(DEPT) + GROUPING(NAME) > 0 ;
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 <--
총무부 강모리 3 1570000 0 0
구하라 4 840000 0 0
저미주 3 327000 0 0
소계 10 2737000 0 1 <--
강모리 3 1570000 1 0 <--
구하라 8 1710000 1 0 <--
이주리 3 480000 1 0 <--
저미주 7 775000 1 0 <--
총계 21 4535000 1 1 <--
|
| |
DEPT COUNT(*) SUM(SALARY) GROUPING(DEPT) GROUPING(NAME)
----------------------------------------------------------------------------------
인사부 11 1798000 0 1
총무부 10 2737000 0 1
총계 21 4535000 1 1
2.1 첫번째 방법
WITH salary AS ( .. )
SELECT DECODE(GROUPING(NAME), 1, (DECODE(GROUPING(DEPT), 1, '총계', DEPT)), DEPT)DEPT
, COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT, NAME)
HAVING GROUPING(DEPT) + GROUPING(NAME) > 0 ;
--2.2 두번째 방법
WITH salary AS ( .. )
SELECT DEPT
, COUNT(*), SUM(SALARY), GROUPING(DEPT)
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY ROLLUP(DEPT) ;
|
| |
TITLE NAME COUNT(*) SUM(SALARY) GROUPING(DEPT) GROUPING(NAME)
----------------------------------------------------------------------------------
부서별총계 인사부 11 1798000 0 1
총무부 10 2737000 0 1
사원별총계 강모리 3 1570000 1 0
구하라 8 1710000 1 0
이주리 3 480000 1 0
저미주 7 775000 1 0
총계 21 4535000 1 1
* 차후 공부를 해서 아래 SQL문은 좀더 TUNING 이 필요할 듯
WITH salary AS ( .. )
SELECT CASE WHEN DEPT = '총계' THEN '총계'
WHEN ROW_NUMBER() OVER(ORDER BY DEPT) = 1 THEN '부서별총계'
WHEN ROW_NUMBER() OVER(ORDER BY NAME) = 1 THEN '사원별총계'
END TITLE
, DECODE(DEPT_ID, 0, DEPT, (DECODE(NAME_ID, 1, '', NAME))) NAME
, CNT, SALARY, DEPT_ID, NAME_ID
FROM (
SELECT DECODE(GROUPING(DEPT) + GROUPING(NAME), 2, '총계', DEPT) DEPT, NAME
, COUNT(*) CNT, SUM(SALARY) SALARY, GROUPING(DEPT) DEPT_ID,GROUPING(NAME) NAME_ID
FROM salary
WHERE SMONTH BETWEEN '01' AND '12'
GROUP BY CUBE(DEPT, NAME)
HAVING GROUPING(DEPT) + GROUPING(NAME) > 0
) A
ORDER BY DEPT_ID, NAME, NAME_ID ; |
