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) eWHERE r=1;

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