學學習網 手機版

學學習網

學習路徑: 學習首頁 > 數據庫 > SQL >

第七節:進階SQL(2)

設置字體:
----------------------------------

算排名

 
列出每一行的排名是一個常見的需求,可惜 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 這兩筆資料),仍然能夠產生正確的排名。
 
 

算中位數

 
要算出中位數,我們必須要能夠達成以下幾個目標:
 
將資料依序排出,并找出每一行資料的排名。
找出『中間』的排名為何。舉例來說,如果總共有 9 筆資料,那中間排名就是 5 (有 4 筆資料比第 5 筆資料大,有 4 筆資料比第 5 筆資料小)。
找出中間排名資料的值。
來看看以下的例子。假設我們有以下的表格:
 
Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要找出中位數,我們就鍵入:
 
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.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) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
 
結果:
 
Median
20
 
讀者將會發現,第 2 行到第 6 行是跟產生 排名 的語句完全一樣。第 7 行則是算出中間的排名。DIV 是在 MySQL 中算出商的方式。在不同的數據庫中會有不同的方式求商。第 1 行則是列出排名中間的資料值。
 
 
算累積總計
算出累積總計是一個常見的需求,可惜以 SQL 并沒有一個很直接的方式達到這個需求。要以 SQL 算出累積總計,基本上的概念與列出排名類似:第一是先做個表格自我連結 (self join),然后將結果依序列出。在做列出排名時,我們算出每一行之前 (包含那一行本身) 有多少行數;而在做累積總計時,我們則是算出每一行之前 (包含那一行本身) 的總合。
----------------------------------
課程列表
重點難點
贊助鏈接
倾国妲己官网 如何用100块钱赚钱吗 吉林十一选五前三直走势图 天津十一选五前三组 上证指数近期走势图 种什么无公害菜最赚钱 快乐飞艇官网 类似金沙棋牌的棋牌游戏 万智对决iso如何赚钱 广东十一选五客服电话 中国的股票指数 七星彩复式排列号 内蒙古11选5计划 最终幻想15水都这么赚钱 三分彩官网注册 包钢稀土股票行情 排列中奖规则及奖金