SQL之18深入group by

複合列 group by rollup(department_id,(job_id,manager_id));[@more@]

SQL> select department_id,job_id,manager_id,sum(salary)
2 from employees
3 group by rollup(department_id,(job_id,manager_id));

DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
SA_REP 149 7000
7000
10 AD_ASST 101 4400
10 4400
20 MK_MAN 100 13000
20 MK_REP 201 6000
20 19000
50 ST_MAN 100 5800
50 ST_CLERK 124 11700
50 17500
60 IT_PROG 102 9000
60 IT_PROG 103 48000
60 57000
80 SA_MAN 100 10500
80 SA_REP 149 19600
80 30100
90 AD_VP 100 34000
90 AD_PRES 24000
90 58000
110 AC_MGR 101 12000
110 AC_ACCOUNT 205 8300
110 20300
213300

23 rows selected.
如果不使用複合列，如下
SQL> ;
1 select department_id,job_id,manager_id,sum(salary)
2 from employees
3* group by rollup(department_id,job_id,manager_id)
SQL> /

DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
SA_REP 149 7000
SA_REP 7000
7000
10 AD_ASST 101 4400
10 AD_ASST 4400
10 4400
20 MK_MAN 100 13000
20 MK_MAN 13000
20 MK_REP 201 6000
20 MK_REP 6000
20 19000
50 ST_MAN 100 5800
50 ST_MAN 5800
50 ST_CLERK 124 11700
50 ST_CLERK 11700
50 17500
60 IT_PROG 102 9000
60 IT_PROG 103 48000
60 IT_PROG 57000
60 57000
80 SA_MAN 100 10500
80 SA_MAN 10500
80 SA_REP 149 19600
80 SA_REP 19600
80 30100
90 AD_VP 100 34000
90 AD_VP 34000
90 AD_PRES 24000
90 AD_PRES 24000
90 58000
110 AC_MGR 101 12000
110 AC_MGR 12000
110 AC_ACCOUNT 205 8300
110 AC_ACCOUNT 8300
110 20300
213300

36 rows selected.