oracle中的roll up,ORACLE SQL 超聚集运算GROUP BY ROLL UP CUBE

第十七章:增强group by(超聚集运算)

--计算部门编号和工作两列联合分组计算

select deptno,job,sum(sal)

from emp

group by deptno,job

order by deptno;

--rollup方式增强:

select deptno,job,sum(sal)

from emp

group by rollup(deptno,job)

order by deptno;

select deptno,job,sum(sal)

from emp

group by deptno,job

union

select deptno,null,sum(sal)

from emp

group by deptno

union

select null,null,sum(sal)

from emp

order by deptno;

--group by rollup(a,b,c) 会产生n+1种分组计算的结果

group by a

union

group by a,b

union

group by a,b,c

union

total

--cube方式增强:

select deptno,job,sum(sal)

from emp

group by cube(deptno,job)

order by deptno;

--group by cube(a,b,c),产生2的n次方种分组计算结果

group by a

union

group by b

union

group by c

union

group by a,b

union

group by b,c

union

group by a,c

union

group by a,b,c

union

total

--复合列的group by

select deptno,job,mgr,sum(sal) from emp

group by rollup(deptno,(job,mgr));

括号中的两列当作一列进行group by：

deptno --> A

(job,mgr) --> B

group by A,B

--grouping sets

select deptno,job,sum(sal) from emp

group by grouping sets

(deptno,job),(job);

返回的结果相当于：

group by deptno,job

union all

group by job

--grouping:区分小计和总计，grouping(deptno) , deptno 为空值的行 grouping 返回 1

select deptno,job,sum(sal),grouping(deptno) d,grouping(job) j

from emp

group by rollup(deptno,job)

order by deptno;

--借助grouping的标识打印“小计”和“总计“

select deptno,job,sum(sal),grouping(deptno)||grouping(job) f

from emp

group by rollup(deptno,job)

order by deptno;

select decode(grouping(deptno)||grouping(job),'01','小计','11','总计',deptno) deptno,job,sum(sal)

from emp

group by rollup(deptno,job);

--grouping_id : 返回所有grouping列组合位向量(二进制值)的十进制数:

col f for a4

select deptno,job,sum(sal),grouping(deptno)||grouping(job) f,grouping_id(deptno,job) b

from emp

group by rollup(deptno,job)

order by deptno;

DEPTNO JOB SUM(SAL) F B

------ --------- ---------- ---- ----------

10 CLERK 1300 00 0

10 MANAGER 2450 00 0

10 PRESIDENT 5000 00 0

10 8750 01 1

20 ANALYST 6000 00 0

20 CLERK 1900 00 0

20 MANAGER 2975 00 0

20 1087 01 1

30 CLERK 950 00 0

30 MANAGER 2850 00 0

30 SALESMAN 5600 00 0

30 9400 01 1

29025 11(二进制) 3 (十进制)

--打印如下结果集

DEPTNO JOB SUM(SAL)

------ --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

subtotal 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

subtotal 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

subtotal 9400

total 29025

select decode(grouping_id(deptno,job),1,'SUBTOTAL',3,'TOTAL',deptno) deptno,

job,

sum(sal)

FROM scott.emp a

GROUP BY ROLLUP(deptno,job);

select rownum,deptno,sum(sal),grouping(rownum) from emp group by rollup(deptno,rownum);

select deptno,rownum,grouping_id(deptno,rownum) flag,decode(grouping_id(deptno,rownum),3,'TOTAL',1,'SUBTOTAL',deptno) deptno,sum(sal)

from emp

group by rollup(deptno,rownum);

select deptno,job,grouping_id(deptno,job),

sum(sal)

FROM scott.emp a

GROUP BY ROLLUP(deptno,job);

select decode(grouping_id(deptno,job),3,'TOTAL',1,'SUBTOTAL',deptno) deptno,

job,

sum(sal)

FROM scott.emp a

GROUP BY ROLLUP(deptno,job);

select decode(grouping_id(deptno,job),3,'All departments''s sal '||'TOTAL',1,'department '||a.deptno||' '||'SUBTOTAL',deptno) deptno,

job,

sum(sal)

FROM scott.emp a

GROUP BY ROLLUP(deptno,job);

select decode(grouping_id(t2.deptno,rownum),3,'总计',1,'小计',t2.deptno) deptno,

decode(GROUPING(ROWNUM), 1, NULL, MAX(ENAME)) ENAME,

SUM(t1.sal)

FROM scott.emp t1, scott.dept t2

WHERE t1.deptno = t2.deptno

GROUP BY ROLLUP(t2.deptno,rownum);