先建了張測試表

SQL> select * from test_a;

ID PLAYNAME SCORE

-------------------- -------------------- ----------

01 aa 100

02 aa 101

02 bb 99

03 bb 98

04 aa 101

02 aa 101

需求是，將score降序排序，列印所有欄位，並且如果是同一個playname的score只取出最高分，如果這個playname獲得過多個相同的最高分，則只取出其中一個（比如：aa獲得過3次101，則只取其中一個），最終要的結果就是：

RK ID PALYNAME SCORE

---------- -------------------- -------------------- ----------

1 02 aa 101

1 02 bb 99

本來我想用max函式，結果直接就出來了：

SQL> select max(score),palyname from test_a group by palyname;

MAX(SCORE) PALYNAME

---------- --------------------

101 aa

99 bb

但是要列印所有欄位…OTL

即使用了巢狀，還是無法解決重複重現最高分的現象：

SQL> select distinct * from test_a t where score in (select max(score) from test_a group by palyname) order by score desc;

ID PALYNAME SCORE

-------------------- -------------------- ----------

02 aa 101

04 aa 101

02 bb 99

由於相同的playname對應的id不同，所以用distinct也無法過濾掉相同playname的並列最高分。

於是只好用rank（）了

Rank的基本語法為：

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

例子1：

　　TABLE：A （科目，分數）

　　數學，80

　　語文，70

　　數學，90

　　數學，60

　　數學，100

　　語文，88

　　語文，65

　　語文，77

　　現在我想要的結果是：（即想要每門科目的前3名的分數）

　　數學，100

　　數學，90

　　數學，80

　　語文，88

　　語文，77

　　語文，70

　　那麼語句就這麼寫：

　　select * from (select rank() over(partition by 科目 order by 分數 desc) rk,a.* from a) t

where t.rk<=3;

以科目來分組，然後以分數來排序，給排序的結果分配rank，取前三名的rank

例子2：

　　有表Table內容如下

　　COL1　COL2

　　　 1　1

　　　 2　1

　　　 3　2

　　　 3　1

　　　 4　1

　　　 4　2

　　　 5　2

　　　 6　2

　　分析功能：列出Col2分組後根據Col1排序,並生成數字列。比較實用於在成績表中查出各科前幾名的資訊。

　　SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;

　　結果如下：

　　COL1　COL2　Rank

　　　 1　1　　 1

　　　 2　1　　 2

　　　 3　1　　 3

　　　 4　1　　 4

　　　 3　2　　 1

　　　 4　2　　 2

　　　 5　2　　 3

　　　 6　2　　 5

這個例子更直觀一點，根據col2分組，根據clo1排序，我們可以發現：

5　2　　 3

6　2　　 5

即，如果兩行記錄完全相同，他們會被給予相同的rank，而排在它們之後的那行記錄，由於前面的並列第3，使得之後的那條記錄變成了第5，而如果我們在這裡用的是dense_rank，那麼之後的那條會變成第4

例子3：

　　合計功能：計算出數值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以後的位置

　　SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;

　　結果如下：

　　Rank

　　4

透過以上方法，得出col1為4，col2為1的那行資料的rank排名為多少

Dense_rank的例子：

dense_rank與rank()用法相當，但是有一個區別：dence_rank在並列關係是，相關等級不會跳過。rank則跳過

　　例如：表

　　A　　　　　　B　　　　　　C

　　a　　　　　liu　　　　　wang

　　a　　　　　jin　　　　　shu

　　a　　　　　cai　　　　　kai

　　b　　　　　yang　　　　 du

　　b　　　　　lin　　　　　ying

　　b　　　　　yao　　　　　cai

　　b　　　　　yang　　　　 99

　　例如：當rank時為：

　　select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　 kai　　　　　1

　　 a　　　　　jin　　　　　 shu　　　　　2

　　 a　　　　　liu　　　　　 wang　　　　 3

　　 b　　　　　lin　　　　　 ying　　　　 1

　　 b　　　　　yang　　　　　du　　　　　 2

　　 b　　　　　yang　　　　　99　　　　　 2

　　 b　　　　　yao　　　　　 cai　　　　　4

　　而如果用dense_rank時為：

　　select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　kai　　　　　 1

　　 a　　　　　jin　　　　　shu　　　　　 2

　　 a　　　　　liu　　　　　wang　　　　 3

　　 b　　　　　lin　　　　　ying　　　　 1

　　 b　　　　　yang　　　　 du　　　　　 2

　　 b　　　　　yang　　　　 99　　　　　 2

　　 b　　　　　yao　　　　　cai　　　　　 3

那麼再回到之前的那個需求，

SQL> select distinct * from (select rank() over(partition by playname order by score desc,id) rk,t.* from test_a t) where rk=1;

RK ID PLAYNAME SCORE

---------- -------------------- -------------------- ----------

1 02 aa 101

1 02 bb 99

這裡order by score desc,id 以score降序和id這兩個欄位排序，也就是說，正因為相同的playname對應的id不同，這樣相同的playname，相同的score，但是不同的id，這樣的2行資料就獲得了不同的rank，而rk=1，即是隻取rank=1，也就是最高分。這樣就完成了需求。

oracle聚合函式rank()的用法和一些體會

相關文章