oracle over()分析函数的使用实例

表数据：

1、按部门求薪酬总和

SELECT t.*,SUM(sal) over(PARTITION BY deptno) 部门薪酬总和 　　
from scott.emp t;

2、按部门累加薪酬

SELECT t.*, SUM(sal) over(PARTITION BY deptno) 部门薪酬总和, SUM(sal) over(PARTITION BY deptno ORDER BY sal) 部门薪酬累计
from scott.emp t;

3、按岗位求薪酬等级

SELECT t.*,rank() over(PARTITION BY t.job ORDER BY t.sal DESC) AS 岗位薪酬等级 　　
from scott.emp t;

4、按部门求薪酬最高者

SELECT e.*
FROM (SELECT t.*,rank() over(PARTITION BY t.deptno ORDER BY t.sal DESC) AS r
     from scott.emp t) e
WHERE r=1;

参考链接： http://edu.cnzz.cn/200912/65231234.shtml