初始化数据
创建测试表group_test
create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director', 'Hill',1000);
insert into group_test values (20,'Coding', 'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director', 'Michael',2000);
insert into group_test values (30,'Coding', 'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director', 'Sabrina',3000);
insert into group_test values (40,'Coding', 'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director', 'Wendy',4000);
commit;
1、初始数据
GROUP_ID JOB NAME SALARY
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000
2、先看一下普通分组的效果:对group_id进行普通的group by操作—按照小组进行分组
> select group_id,sum(salary) from group_test group by group_id;
GROUP_ID SUM(SALARY)
---------- -----------
30 12000
20 8000
40 16000
10 4000
3、对group_id进行普通的rollup操作—按照小组进行分组,同时求总计
> select group_id,sum(salary) from group_test group by rollup(group_id);
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
使用Group By语句翻译一下上面的SQL语句如下:
> select group_id,sum(salary) from group_test group by group_id
> union all
> select null, sum(salary) from group_test
> order by 1;
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
4、再看一个rollup两列的情况
> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Coding 1000
10 Director 1000
10 Architect 1000
10 Programmer 1000
10 4000
20 Coding 2000
20 Director 2000
20 Architect 2000
20 Programmer 2000
20 8000
30 Coding 3000
30 Director 3000
30 Architect 3000
30 Programmer 3000
30 12000
40 Coding 4000
40 Director 4000
40 Architect 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
使用Group By语句翻译一下上面的SQL语句如下:
> select group_id,job,sum(salary) from group_test group by group_id, job
> union all
> select group_id,null,sum(salary) from group_test group by group_id
> union all
> select null,null,sum(salary) from group_test
> order by 1,2;
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Architect 1000
10 Coding 1000
10 Director 1000
10 Programmer 1000
10 4000
20 Architect 2000
20 Coding 2000
20 Director 2000
20 Programmer 2000
20 8000
30 Architect 3000
30 Coding 3000
30 Director 3000
30 Programmer 3000
30 12000
40 Architect 4000
40 Coding 4000
40 Director 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
5、补充一步,体验一下GROUPING函数的效果
> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Coding 0 0 1000
10 Director 0 0 1000
10 Architect 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Architect 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Architect 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Architect 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
1 1 40000
21 rows selected.
GROUPING函数对应的列(例如JOB字段)是由ROLLUP函数所产生的空值则显示“1”,即对此列进行汇总计算后的结果。
如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。
结果其实是对初始数据集进行group_id和job分组,然后根据group_id进行汇总,最后在之前(根据group_id进行汇总后)的基础上根据job汇总。
如果将rollup中的两个列交换会有什么情况呢?请看:
> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(job,group_id);
结果其实是对初始数据集进行job和group_id(注意顺序)分组,然后根据job进行汇总,最后在之前(根据job进行汇总后)的基础上根据group_id汇总。
如果将rollup中job移到外面呢?
select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id),job;
结果其实是对初始数据集进行job和group_id(注意顺序,可以看到其实rollup的执行顺序靠后,将rollup和job换个位置结果一样)分组,然后根据job进行汇总,这里就没有在之前(根据job进行汇总后)的基础上根据group_id汇总了。
部分转载自:来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-610995/