oracle分组函数之ROLLUP演示
来源:互联网 发布:拓扑图布局算法 编辑:程序博客网 时间:2024/05/18 00:45
1.初始化实验坏境
1)创建测试表group_test
sec@ora10g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
Table created.
2)初始化数据
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;
3)初始化之后的数据情况如下:
sec@ora10g> select * from group_test;
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
16 rows selected.
2.先看一下普通分组的效果:对group_id进行普通的group by操作---按照小组进行分组
sec@ora10g> 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进行普通的roolup操作---按照小组进行分组,同时求总计
sec@ora10g> 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
翻译一下上面的SQL语句如下(union all一个统计所有数据的行):
sec@ora10g> select group_id,sum(salary) from group_test group by group_id
2 union all
3 select null, sum(salary) from group_test
4 order by 1;
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
4.再看一个rollup两列的情况
sec@ora10g> 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.
上面的SQL语句该如何翻译呢?
如下:
sec@ora10g> select group_id,job,sum(salary) from group_test group by group_id, job
2 union all
3 select group_id,null,sum(salary) from group_test group by group_id
4 union all
5 select null,null,sum(salary) from group_test
6 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
直接看效果就OK了:
sec@ora10g> 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.
看出来什么效果了么?
有的同学还是没有看出来,小小的解释一下:
如果显示“1”表示此行对应的这列参与了分组活动
如果显示“0”表示此行对应的这列参未与了分组活动
解释完毕
6.小结
ROLLUP在数据统计和报表生成过程中很有帮助,而且效率比起来union之类的高。这也体现了oracle在SQL统计上人性化、自动化的特点。
另外可以参考oracle官方文档中的例子,链接如下:
《ROLLUP Extension to GROUP BY》
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#i1007413
-- The End --
- oracle分组函数之ROLLUP演示
- oracle分组函数之ROLLUP演示
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
- oracle分组函数之ROLLUP
- Oracle分组函数之ROLLUP
- Oracle分组函数之ROLLUP
- 【实验】【CUBE】oracle分组函数之CUBE演示及与ROLLUP的比较
- 【CUBE】oracle分组函数之CUBE演示及与ROLLUP的比较
- oracle分组函数之CUBE演示及与ROLLUP的比较
- Oracle分组函数之ROLLUP魅力
- 百度博客有些蹩脚,想把博客移到csdn上,但csdn咋没有批量导入百度博客的功能,怎么办?
- pku 3753 根据关键字进行字符串拷贝 解题报告
- DateSet使用技巧
- 使用PING和Traceroute 检查网络的连通性
- ARP学习总结
- oracle分组函数之ROLLUP演示
- 使用C语言扩展Python(三)
- Excel中列数字与字母的转换(JAVA实现)
- 使用C语言扩展Python(二)
- Android浏览器插件开发(一)
- 使用PING和Traceroute 检查网络的连通性
- Flex 计算String的宽度
- 创建窗体
- Linux下 QT的一些问题集锦