---------------------------------------------------------------------------------------------------------------------------------
Oracle分组函数之cube与rollup(举例说明)
1.甩表:
create table TAB
(
GROUP_ID VARCHAR2(32),
JOB VARCHAR2(32),
NAME VARCHAR2(32),
SALARY VARCHAR2(32)
);
2.执行脚本,附截图:
|-2.1
select t.* from tab t ;
|-2.2
select group_id,job,grouping(group_id),grouping(job),sum(salary)
from tab group by rollup(group_id, job);
|-2.3
select group_id,job,grouping(group_id),grouping(job),sum(salary)
from tab group by cube(group_id, job) order by 1;
3.分析:
关键字 | 统计列 |
rollup(a,b) | (a,b)、(a)、() |
rollup(a,b,c) | (a,b,c)、(a,b)、(a)、() |
cube(a,b) | (a,b)、(a)、(b)、() |
cube(a,b,c) | (a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、() |