SQL語法 | PL/SQL | Function | TRIGGER | StoreProc | DBA

2009年9月4日 星期五

SQL 算排名

列出每一行的排名是一個常見的需求,可惜 SQL 並沒有一個很直接的方式達到這個需求。要以 SQL 列出排名,基本的概念是要做一個表格自我連結 (self join),將結果依序列出,然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑,所以最好的方式是用一個實例來介紹。假設我們有以下的表格:

Total_Sales 表格

Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20

要找出每一行的排名,我們就打入以下的 SQL 語句:

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

結果:

Name Sales Sales_Rank
Greg 50 1
Sophia 40 2
Stella 20 3
Jeff 20 3
Jennifer 15 5
John 10 6

我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales),我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料,那這部分的 WHERE 子句本身就可以產生出正確的排名。

子句的第二部分,(a1.Sales=a2.Sales and a1.Name = a2.Name),則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料),仍然能夠產生正確的排名。

沒有留言: