OLLUP| | 14 | 756 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 448 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------- 可以发现，这种方法不但SQL书写方便，性能也能得到提高。
这时候，如果又有人跑过来说：除了以上数据，他还需要每个职位总的业绩，你只要把rollup换成cube就可以了，如下所示：
-- CUBE分组
SELECT a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
SALES 9400
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 10875
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000 从上面可以看出：cube比rollup的展现的粒度更细一些。
这时候，如果又有人跑过来说：他不需要那么细的数据，只需要汇总的数据，可以使用Grouping Sets：?
---GROUPING SETS分组
SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);
HIRE DNAME JOB SUM_SAL
---- -------------- --------- ----------
1987 4100
1980 800
1982 1300
1981 22825
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000