高级分组rollup,cube操作

按部门分组

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

DEPTNO SUM(SAL)

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

30 9400

20 6775

10 8750

按部门分组,并求总计

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

DEPTNO SUM(SAL)

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

10 8750

20 6775

30 9400

24925

Rollup分组，一次全表扫描

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

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

分解为下列语句

select deptno,sum(sal) from emp group by deptno

union all

select null,sum(sal) from emp

order by 1;

两次扫描表，效率低

Group by Rollup(a,b,c,d)

的结果集为,共n+1个集

Group by a,b,c,d

Union all

Group by a,b,c

Union all

Group by a,b

Union all

Group by a

Union all

Group by null

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

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 800

20 ANALYST 3000

20 MANAGER 2975

20 6775

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

24925

结果为

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,null,sum(sal) from emp;

Grouping(列名称）的使用，为了表达该列是否参加了分组活动。

0为该列参加了分组，1为该列未参加分组操作

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

from emp group by rollup(deptno,job);

DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL)

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

10 CLERK 0 0 1300

10 MANAGER 0 0 2450

10 PRESIDENT 0 0 5000

10 0 1 8750

20 CLERK 0 0 800

20 ANALYST 0 0 3000

20 MANAGER 0 0 2975

20 0 1 6775

30 CLERK 0 0 950

30 MANAGER 0 0 2850

30 SALESMAN 0 0 5600

30 0 1 9400

select deptno,job,grouping(deptno),

grouping(job) ,sum(sal) from emp group by cube(deptno,job);

结果集为，2**n个结果集

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,job,sum(sal) from emp group by job

union all

select null,null,sum(sal) from emp;