over partition by与group by 的区别
来源:互联网 发布:sql小数点后保留两位 编辑:程序博客网 时间:2024/05/21 17:03
原文:http://www.itpub.net/viewthread.php?tid=1001044
over partition by 与 group by 都是与统计类函数用,这两个有什么区别呢?
目前我只知道一个这样的区别:
比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000 SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM DUAL UNION ALL
SELECT 'E',10,1000 FROM DUAL;
NAME DEPT SALARY
A 10 1000
B 10 2000
C 20 1500
D 20 3000
E 10 1000
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资:
select name,dept,salary,sum(salary) over (partition by dept) total_salary from salary;
name dept salary tatal_salary
A 10 1000 4000
B 10 2000 4000
E 10 1000 4000
C 20 1500 4500
D 20 3000 4500
用goup by 就没办法做到这点,只能查询到每个部门的总工资:
select dept,sum(salary) total_salary from salary group by dept
dept total_salary
10 4000
20 4500
另外over partion by 还可以做到查询每位员工占部门总工资的百分比:
select name,dept,salary,salary*100/sum(salary) over (partition by dept) percent from salary;
name dept salary percent
A 10 1000 25
B 10 2000 50
E 10 1000 25
C 20 1500 33.3333333333333
D 20 3000 66.6666666666667
用group by 也没办法做到这个.不知道我的理解正不正确,请各位朋友指点,特别是over partition by 与group by 的更多区别请各位一起分享,谢谢!
20 4500
- over partition by与group by 区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- over partition by与group by 的区别
- group by 与 partition by的区别
- over partition by与group by
- partition by 与group by 区别
- oracle分析函数over partition by 和group by的区别
- partition by与group by
- group by 与 partition by
- sum over用法,以及与group by的区别
- IDL不支持方法重载
- perl语言 两个关键字:BEGIN, END 介绍
- SQL中的With语句
- 通过unshift增加use,required 包命令路径
- 通过unshift增加use,required 包命令路径
- over partition by与group by 的区别
- 文本检查与过滤
- const使用总结
- WinMain函数
- 从ubuntu中文论坛转载的一片超好的文章,慢慢学习中
- 性能测试,你的优势在哪里?
- asp email jmail cdo
- WindowProc函数
- POJ1463 树型动态规划