Oracle中rollup，cube，grouping sets在group by中的不同作用

        本文通过实例操作向大家介绍Oracle中有关group by 的rollup、cube、grouping sets。

       先来看下emp表的基本结构及表中的数据。

SQL> desc emp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select * from emp;

ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- --------- ---------- -------------- ---------- ---------- ----------
SMITH      CLERK           7902 17-12月-80            800                    20
ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
WARD       SALESMAN        7698 22-2月 -81           1250        500         30
JONES      MANAGER         7839 02-4月 -81           2975                    20
MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
BLAKE      MANAGER         7839 01-5月 -81           2850                    30
CLARK      MANAGER         7839 09-6月 -81           2450                    10
SCOTT      ANALYST         7566 19-4月 -87           3000                    20
KING       PRESIDENT            17-11月-81           5000                    10
TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
ADAMS      CLERK           7788 23-5月 -87           1100                    20

ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- --------- ---------- -------------- ---------- ---------- ----------
JAMES      CLERK           7698 03-12月-81            950                    30
FORD       ANALYST         7566 03-12月-81           3000                    20
MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

　　本文中主要涉及到的列有job（职位）、sal（工资）、deptno(部门号）。大体思路是通过不同条件对工资进行汇总,用到的函数有sum（sal）。  按部门、职位进行分组对工资进行基本汇总，对属于同一部门又在是相同职位的职员进行工资求和，结果如下。

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by deptno,job
  4  order by deptno, job;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
CLERK             10       1300
MANAGER           10       2450
PRESIDENT         10       5000
ANALYST           20       6000
CLERK             20       1900
MANAGER           20       2975
CLERK             30        950
MANAGER           30       2850
SALESMAN          30       5600

已选择9行。

 　　加上统计条件rollup进行统计。得到如下结果。

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by rollup(deptno,job)
  4  order by deptno,job;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
CLERK             10       1300
MANAGER           10       2450
PRESIDENT         10       5000
                  10       8750
ANALYST           20       6000
CLERK             20       1900
MANAGER           20       2975
                 20      10875
CLERK             30        950
MANAGER           30       2850
SALESMAN          30       5600

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
                 30       9400
                         29025

已选择13行。

　　大家会发现与上面汇总不同，加上rollup后每个部门（不是职位）会进行一次小汇总,比如10部门1300+2450+5000=8750，而且最后进行一次总汇总。大家会奇怪为什么不是对职位进行小汇总而是部门？继续往下看↓。

SQL> select job,deptno,sum(sal)
  2  from emp
  3  group by rollup(job,deptno)
  4  order by deptno,job;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
CLERK             10       1300
MANAGER           10       2450
PRESIDENT         10       5000
ANALYST           20       6000
CLERK             20       1900
MANAGER           20       2975
CLERK             30        950
MANAGER           30       2850
SALESMAN          30       5600
ANALYST                    6000
CLERK                      4150
JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
MANAGER                    8275
PRESIDENT                  5000
SALESMAN                   5600
                          29025

已选择15行。

　　大家由结果可以分析出，与上次的rollup对比本次是对职位进行了小汇总，总汇总也仍然存在。而两次语句不同之处在于rollup()中字段的先后顺序上面的是rollup(deptno,job)，而本次是rollup(job,deptno)，谁在前对谁进行小汇总。接着看cube。

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by cube(deptno,job)
  4  order by deptno, job;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
CLERK             10       1300
MANAGER           10       2450
PRESIDENT         10       5000
                  10       8750
ANALYST           20       6000
CLERK             20       1900
MANAGER           20       2975
                  20      10875
CLERK             30        950
MANAGER           30       2850
SALESMAN          30       5600

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
                 30       9400
ANALYST                    6000
CLERK                      4150
MANAGER                    8275
PRESIDENT                  5000
SALESMAN                   5600
                          29025

已选择18行。

　　容易分析出，加了cube的group by语句执行结果中，既有基本汇总，又有部门小汇总和职位小汇总，还有总汇总比较全面。最后来分析grouping sets的作用。

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by grouping sets(deptno,job)
  4  order by deptno,job;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
                  10       8750
                  20      10875
                  30       9400
ANALYST                    6000
CLERK                      4150
MANAGER                    8275
PRESIDENT                  5000
SALESMAN                   5600

已选择8行。

　　由结果可以看出grouping sets的查询结果只是进行了小汇总而已,没有总汇总。

　　希望对大家有所帮助！