高级分组rollup,cube操作(三)
0 0
30 27390 0 1
30 12100 0 0
30 15290 0 0
40 7150 0 1
40 7150 0 0
50 172040 0 1
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
50 40040 0 0
50 70730 0 0
50 61270 0 0
60 31680 0 1
60 31680 0 0
70 11000 0 1
70 11000 0 0
80 304500 0 1
80 61000 0 0
80 243500 0 0
90 58000 0 1
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
90 34000 0 0
90 24000 0 0
100 51600 0 1
100 12000 0 0
100 39600 0 0
110 20300 0 1
110 12000 0 0
110 8300 0 0
52 rows selected.
结果集为，2**n(2的N次方)个结果集
select DEPARTMENT_ID,job_id,sum(salary) from emp group by DEPARTMENT_ID,job_id
union all
select DEPARTMENT_ID,null,sum(salary) from emp group by DEPARTMENT_ID
union all
select null,job_id,sum(salary) from emp group by JOB_ID
union all
select null,null,sum(salary) from emp;