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

http://cbf0713.blog.163.com/blog/static/9077890520102591928645/

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 子句。