现在的位置: 首页 > 综合 > 正文

SQL语句中的rank () over , row_number() over ,rank_dense (), PARTITION BY

2013年03月15日 ⁄ 综合 ⁄ 共 2271字 ⁄ 字号 评论关闭
程序员最喜欢的衬衫

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

以下實現分組後按組產生序號。

RANK() OVER (PARTITION BY DepartmentName
ORDER BY num)  top20 

抱歉!评论已关闭.