oracle分析函数row_number() over()使用

g3hk52t4ql

　　row_number() over ([partition by col1] order by col2) ) as 别名
表示根据col1分组，在分组内部根据 col2排序
而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略。
　　以Scott/tiger登陆，以emp表为例。
　　1、select deptno,ename,sal,
     sum(sal) over (order by ename) 累计, --按姓名排序，并将薪水逐个累加
     sum(sal) over () 总和 ,               -- 此处sum(sal) over () 等同于sum(sal)，求薪水总和
    100*round(sal/sum(sal) over (),4) "份额(%)" --求每个人的薪水占总额的比例，小数点后保留2位，括号和百分号为特殊符号，所以需要“”
    from emp
　　结果如下 ：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/f2598dedc472bc3f63d09f66.jpg
　　2、select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,--partition by deptno先按部门分组，再按姓名排序，并将薪水逐个累加
sum(sal) over (partition by deptno) 部门总和,   -- 每个部门的薪水总和
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",--每个员工在各自部门的薪水比例
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) "总份额(%)" --求每个人的薪水占总额的比例
from emp
　　结果如下：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/4400ed169f5bb811c83d6d62.jpg
　　3、select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,--根据部门分组，再按部门内的个人薪水排序，逐个累加。
sum(sal) over (order by deptno,sal) sum --按部门排序，将薪水逐个累加。
from emp;
　　结果如下：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/8156d302c3e47ff409fa9368.jpg
　　4、部门从大到小排列，部门里各员工的薪水从高到低排列
　　select deptno,ename,sal,
   sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,--按部门分组后，按部门和薪水降序排
   sum(sal) over (order by deptno desc,sal desc) sum --按部门和薪水降序排
   from emp;
　　结果如下：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/e9d39718cbaa685d42a9ad6b.jpg
       
　　5、将各部门的员工按薪水排序
　　select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 --先按部门分组，再在部门中按薪水降序排名
from scott.emp
　　结果如下：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/332428f5a7f714f77609d79f.jpg
　　6、查找各部门中薪水最高的前2位
　　select ename,job,deptno,sal,排名 from
(select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名
from scott.emp                   --先将各部门的员工按薪水排序，再在结果中取出需要的部分
) where 排名<=2;
　　结果如下：
http://hiphotos.baidu.com/%D3%F4%C3%C6%BB%A8%C9%FA%BD%B4/pic/item/4ee8f9c5ad825ed339db499d.jpg
　　7、如果已经在over()中进行过分组，在"... from emp;"后面不要加order   by 子句。