SQL語句中的rank () over ， row_number() over ，rank_dense ()

總結如下：

我建立了一個表，資料如下，

SQL> select * from test;

        A1         A2
---------- ----------
         1          3
         2          4
         3          2
         3          5
         4          2

然後用rank () over，

SQL> select a1,a2,rank () over (order by a1) rank from test;

        A1         A2       RANK
---------- ---------- ----------
         1          3          1
         2          4          2
         3          2          3
         3          5          3
         4          2          5

SQL> select a1,a2,rank () over (order by a2) rank from test;

        A1         A2       RANK
---------- ---------- ----------
         3          2          1
         4          2          1
         1          3          3
         2          4          4
         3          5          5

原來這個就是用來看排名的東東啊。

接下來看看row_number() over，

SQL> select a1,a2, row_number () over (order by a1) rank from test;

        A1         A2       RANK
---------- ---------- ----------
         1          3          1
         2          4          2
         3          2          3
         3          5          4
         4          2          5

SQL> select a1,a2, row_number () over (order by a2) rank from test;

        A1         A2       RANK
---------- ---------- ----------
         3          2          1
         4          2          2
         1          3          3
         2          4          4
         3          5          5

黃色標出了區別，這個很容易看清楚吧~~~

再看看dense_rank () over

SQL> select a1,a2, dense_rank () over (order by a1) rank from test;

        A1         A2       RANK
---------- ---------- ----------
         1          3          1
         2          4          2
         3          2          3
         3          5          3
         4          2          4

dense_rank在有出現相同的rank後，rank的值是緊接上一次的rank值。

可以這麼理解，我們通常意義上的top N 的實現應該採用row_number() over 而不是rank () over，因為row_number 是給記錄加一個序號標記，而rank 是相當於進行排序後的排名，也就是如果排序列是相同的，那麼他們的返回值也是相同的，而row_number則不可能返回相同值。

放到一起看看吧：

SQL> select a1,a2,
2 rank () over (order by a1) rank,
3 dense_rank () over (order by a1) dense_rank,
4 row_number () over (order by a1) row_number
5 from test;

        A1         A2       RANK DENSE_RANK ROW_NUMBER
---------- ---------- ---------- ---------- ----------
         1          3          1          1          1
         2          4          2          2          2
         3          2          3          3          3
         3          5          3          3          4
         4          2          5          4          5

row_number()和rownum差不多，功能更強一點（可以在各個分組內從1開時排序）．

　　rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）．

　　dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重複值的