[转]over()函数的使用
oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.sql over的作用及用法RANK ( ) OVER ( [query_partition_clause] order_by_clause )DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,其中PARTITION BY 为分组字段，ORDER BY 指定排序字段over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。其参数：over（partition by columnname1 order by columnname2）含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。例如：employees表中，有两个部门的记录：department_id ＝10和20select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)--所有人的总工资select a.empno, a.ename, sum(a.sal) over() total from emp a;EMPNOENAMETOTAL7369SMITH290257499ALLEN290257521WARD290257566JONES290257654MARTIN290257698BLAKE290257782CLARK290257788SCOTT290257839KING290257844TURNER290257876ADAMS290257900JAMES290257902FORD290257934MILLER29025--2、over(partition by ...) 分组统计--统计部门的平均工资select a.empno, a.ename, b.dname, to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg from emp a, dept b where a.deptno = b.deptno;EMPNOENAMEDNAME DEPT_AVG7934MILLERACCOUNTING $2,916.677839KINGACCOUNTING $2,916.677782CLARKACCOUNTING $2,916.677876ADAMSRESEARCH $2,175.007902FORDRESEARCH $2,175.007566JONESRESEARCH $2,175.007369SMITHRESEARCH $2,175.007788SCOTTRESEARCH $2,175.007521WARDSALES $1,566.677844TURNERSALES $1,566.677499ALLENSALES $1,566.677900JAMESSALES $1,566.677698BLAKESALES $1,566.677654MARTINSALES $1,566.67--查询出管理员工人数最多的人的名字和他管理的人的名字select b.ename, t.ename, t.mgr, t.cnt from (select a.empno, a.ename, a.mgr, count(1) over(partition by a.mgr) cnt from emp a) t, emp b where t.mgr = b.empno;ENAMEENAMEMGRCNTJONESSCOTT75662JONESFORD75662BLAKEWARD76985BLAKETURNER76985BLAKEALLEN76985BLAKEJAMES76985BLAKEMARTIN76985CLARKMILLER77821SCOTTADAMS77881KINGBLAKE78393KINGJONES78393KINGCLARK78393FORDSMITH79021--3、over(order by ...) 排序统计select a.empno, a.deptno, a.ename, a.sal, sum(a.sal) over(order by a.ename) sum from emp a;EMPNODEPTNOENAMESALSUM787620ADAMS1100.001100749930ALLEN1600.002700769830BLAKE2850.005550778210CLARK2450.008000790220FORD3000.0011000790030JAMES950.0011950756620JONES2975.0014925783910KING5000.0019925765430MARTIN1250.0021175793410MILLER1300.0022475778820SCOTT3000.0025475736920SMITH800.0026275784430TURNER1500.0027775752130WARD1250.0029025--4、over(partition by ... order by ...) 分组排序统计--统计各部门薪水前三名的人员select t.* from (select rank() over(partition by b.dname order by a.sal desc) rk, a.empno, a.ename, b.dname, a.sal from emp a, dept b where a.deptno = b.deptno) t where t.rk <= 3;RKEMPNOENAMEDNAME SAL17839KINGACCOUNTING 5000.0027782CLARKACCOUNTING 2450.0037934MILLERACCOUNTING 1300.0017902FORDRESEARCH 3000.0017788SCOTTRESEARCH 3000.0037566JONESRESEARCH 2975.0017698BLAKESALES 2850.0027499ALLENSALES 1600.0037844TURNERSALES 1500.00

?