rollup和cube区别

rollup和cube是group by 的扩展，都是包含分组合计和总合计，但是也有区别

rollup返回每层分组（从前往后）小合计及总合计
 如：rollup(A,B,C) = (A,B,C)+(A,B)+(A)+总合计
cube 返回每个列组合小合计和总合计
  如：cube(A,B,C)=(A,B,C)+(A,B)+(A,C)+(A)+(B,C)+(B)+(C)+总合计

SCOTT@TEST>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@TEST>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

group by 一个字段
SCOTT@TEST>select job,sum(sal) from emp group by job;

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

SCOTT@TEST>select job,sum(sal) from emp group by rollup(job);

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

已选择6行。

SCOTT@TEST>select job,sum(sal) from emp group by cube(job);

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

已选择6行。

一个字段cube，rollup无区别，都是包含一个总计，
不同的是rollup总计在最后，cube是在最前面

两个字段测试
SCOTT@TEST>select job,mgr,sum(sal) from emp group by rollup(job,mgr);

JOB              MGR   SUM(SAL)
--------- ---------- ----------
CLERK           7698        950
CLERK           7782       1300
CLERK           7788       1100
CLERK           7902        800
CLERK                      4150
ANALYST         7566       6000
ANALYST                    6000
MANAGER         7839       8275
MANAGER                    8275
SALESMAN        7698       5600
SALESMAN                   5600
PRESIDENT                  5000
PRESIDENT                  5000
                          29025

已选择14行。

等同于
select job,mgr,sum(sal) from emp group by job,mgr
union all
select job,null,sum(sal) from emp group by job
union all
select null,null,sum(sal) from emp

SCOTT@TEST>select job,mgr,sum(sal) from emp group by cube(job,mgr);

JOB              MGR   SUM(SAL)
--------- ---------- ----------
                           5000
                          29025
                7566       6000
                7698       6550
                7782       1300
                7788       1100
                7839       8275
                7902        800
CLERK                      4150
CLERK           7698        950
CLERK           7782       1300
CLERK           7788       1100
CLERK           7902        800
ANALYST                    6000
ANALYST         7566       6000
MANAGER                    8275
MANAGER         7839       8275

JOB              MGR   SUM(SAL)
--------- ---------- ----------
SALESMAN                   5600
SALESMAN        7698       5600
PRESIDENT                  5000
PRESIDENT                  5000

已选择21行。

等同于
select job,mgr,sum(sal) from emp group by job,mgr
union all
select job,null,sum(sal) from emp group by job
union all
select null,mgr,sum(sal) from emp group by mgr
union all
select null,null,sum(sal) from emp