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