rollup cube 高级分组函数简单例子

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

1 1 24925

 Cube分组

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;