WITH t AS ( SELECT 'AA5A' dept_id, 300 salary, 100 bonus FROM dual UNION ALL SELECT 'AA5A', 400, 150 FROM dual UNION ALL SELECT 'AA5B', 350, 200 FROM dual UNION ALL SELECT 'AA5B', 100, 400 FROM dual ) SELECT DECODE('', d2, '회사', d3, '사업부', d4, '과', '반') 분류 , COALESCE(d4, d3, d2, d1) dept_id , SUM(salary) salary , SUM(bonus) bonus FROM (SELECT SUBSTR(dept_id, 1, 1) d1 , SUBSTR(dept_id, 1, 2) d2 , SUBSTR(dept_id, 1, 3) d3 , dept_id d4 , salary , bonus FROM t ) GROUP BY d1, ROLLUP(d2, d3, d4) ;
WITH T1 AS ( SELECT 'AA5A' DEPT_ID, 300 SALARY, 100 BONUS FROM DUAL UNION ALL SELECT 'AA5A', 400, 150 FROM DUAL UNION ALL SELECT 'AA5B', 350, 200 FROM DUAL UNION ALL SELECT 'AA5B', 100, 400 FROM DUAL ) , T2 AS ( (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4 ) ) SELECT DECODE(LV,4,'반',3,'과','2','사업부',1,'전체','') "분류" , SUBSTR(DEPT_ID,1,LV ) DEPT_ID , SUM(SALARY)SALARY , SUM(BONUS) BONUS , LV FROM T1 , T2 GROUP BY SUBSTR(DEPT_ID,1,LV ) , LV ORDER BY LV DESC