表数据:
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