ORACLE中OVER函数的用法.docx
《ORACLE中OVER函数的用法.docx》由会员分享，可在线阅读，更多相关《ORACLE中OVER函数的用法.docx（13页珍藏版）》请在冰豆网上搜索。
ORACLE中OVER函数的用法
oracleover函数详解
今天在javaeye上看到一道面试题，很多人都用over函数解决的
特意查了一下它的用法
SQL>selectdeptno,ename,sal
2 fromemp
3 orderbydeptno;
DEPTNOENAME SAL
------------------------------
10CLARK 2450
KING 5000
MILLER 1300
20SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
已选择14行。
2.先来一个简单的，注意over（...）条件的不同，
使用sum（sal）over（orderbyename）...查询员工的薪水“连续”求和,
注意over（order byename）如果没有orderby子句，求和就不是“连续”的，
放在一起，体会一下不同之处：
SQL>selectdeptno,ename,sal,
2 sum（sal）over（orderbyename）连续求和,
3 sum（sal）over（）总和, --此处sum（sal）over（）等同于sum（sal）
4 100*round（sal/sum（sal）over（）,4）"份额（%）"
5 fromemp
6 /
DEPTNOENAME SAL连续求和 总和份额（%）
------------------------------------------------------------
20ADAMS 1100 1100 29025 3.79
30ALLEN 1600 2700 29025 5.51
30BLAKE 2850 5550 29025 9.82
10CLARK 2450 8000 29025 8.44
20FORD 3000 11000 29025 10.34
30JAMES 950 11950 29025 3.27
20JONES 2975 14925 29025 10.25
10KING 5000 19925 29025 17.23
30MARTIN 1250 21175 29025 4.31
10MILLER 1300 22475 29025 4.48
20SCOTT 3000 25475 29025 10.34
20SMITH 800 26275 29025 2.76
30TURNER 1500 27775 29025 5.17
30WARD 1250 29025 29025 4.31
已选择14行。
3.使用子分区查出各部门薪水连续的总和。
注意按部门分区。
注意over（...）条件的不同，
sum（sal）over（partitionbydeptnoorderbyename）按部门“连续”求总和
sum（sal）over（partitionbydeptno）按部门求总和
sum（sal）over（orderbydeptno，ename）不按部门“连续”求总和
sum（sal）over（）不按部门，求所有员工总和，效果等同于sum（sal）。
SQL>selectdeptno,ename,sal,
2 sum（sal）over（partitionbydeptnoorderbyename）部门连续求和,--各部门的薪水"连续"求和
3 sum（sal）over（partitionbydeptno）部门总和, --部门统计的总和，同一部门总和不变
4 100*round（sal/sum（sal）over（partitionbydeptno）,4）"部门份额（%）",
5 sum（sal）over（orderbydeptno,ename）连续求和,--所有部门的薪水"连续"求和
6 sum（sal）over（）总和, --此处sum（sal）over（）等同于sum（sal），所有员工的薪水总和
7 100*round（sal/sum（sal）over（）,4）"总份额（%）"
8 fromemp
9 /
DEPTNOENAMESAL部门连续求和部门总和部门份额（%）连续求和总和 总份额（%）
----------------------------------------------------------------------------
10CLARK2450 2450 8750 28 2450 29025 8.44
KING5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48
20ADAMS1100 1100 10875 10.11 9850 29025 3.79
FORD3000 4100 10875 27.59 12850 29025 10.34
JONES2975 7075 10875 27.36 15825 29025 10.25
SCOTT3000 10075 10875 27.59 18825 29025 10.34
SMITH800 10875 10875 7.36 19625 29025 2.76
30ALLEN1600 1600 9400 17.02 21225 29025 5.51
BLAKE2850 4450 9400 30.32 24075 29025 9.82
JAMES950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD1250 9400 9400 13.3 29025 29025 4.31
已选择14行。
4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL>selectdeptno,ename,sal,sum（sal）over（partitionbydeptnoorderbysal）dept_sum,
2 sum（sal）over（orderbydeptno,sal）sum
3 fromemp;
DEPTNOENAME SALDEPT_SUM SUM
--------------------------------------------------
10MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
20SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
30JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
已选择14行。
5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL>selectdeptno,ename,sal,
2 sum（sal）over（partitionbydeptnoorderbydeptnodesc,saldes
升级会员 