如何使用Oracle的分析函数ROW_NUMBER、DENSE_RANK、RANK

共同点：这三个分析函数都可以在各个分组内从1开始排序。
区别：ROW_NUMBER()是没有重复值的，可以利用它实现分页显示。
DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名。
RANK()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。

SELECT * FROM

(
      SELECT deptno, ename, sal,

      ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) Top3 FROM emp
)
WHERE Top3 <= 3
/

    DEPTNO ENAME                 SAL           TOP3
---------- ---------- ---------- ----------
            10 KING                    5000              1
            10 CLARK                2450              2
            10 MILLER               1300              3

            20 SCOTT                3000              1       <--- !
            20 FORD                  3000              2       <--- !
            20 JONES                 2975              3

            30 BLAKE                2850              1
            30 ALLEN                1600              2
            30 TURNER             1500              3

9 rows selected.

 

SELECT * FROM

(
       SELECT deptno, ename, sal,

       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/

        DEPTNO ENAME                 SAL           TOPN
---------- ---------- ---------- ----------
            10 KING                   5000              1
            10 CLARK                2450              2
            10 MILLER               1300              3

          20 SCOTT                3000              1      <--- !
          20 FORD                 3000              1      <--- !
            20 JONES                2975              2
            20 ADAMS               1100              3

            30 BLAKE                2850              1
            30 ALLEN                1600              2
            30 TURNER             1500              3


10 rows selected.

 

SELECT * FROM

(
      SELECT deptno, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) TopN

      FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/

        DEPTNO ENAME                 SAL           TOPN
---------- ---------- ---------- ----------
            10 KING                  5000               1
            10 CLARK                2450               2
            10 MILLER               1300               3

          20 SCOTT                3000             1      <--- !
          20 FORD                  3000            1      <--- !
            20 JONES                2975             3

            30 BLAKE                2850              1
            30 ALLEN                1600              2
            30 TURNER             1500              3


9 rows selected