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