Oracle的分析函数ROW_NUMBER、DENSE_RANK、RANKOracle的分析函数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