oracle分析函數row_number() over()使用（2）

很好的一個文章，來自http://apps.hi.baidu.com/share/detail/21843741；

 

由於圖片無法引用，不過SQL可以直接啟動並執行。

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

結果如下 ：

 

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

結果如下：

 

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;

結果如下：

 

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;

結果如下：

       

5、將各部門的員工按薪水排序

select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 --先按部門分組，再在部門中按薪水降序排名
from scott.emp

結果如下：

 

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;

結果如下：

 

7、如果已經在over()中進行過分組，在"... from emp;"後面不要加order   by 子句。