Mysql按條件計數的幾種方法

最近在給喜樂喜樂網的後臺新增一系列的統計功能，遇到很多需要按條件計數的情況。嘗試了幾種方法，下面簡要記錄，供大家參考。

問題描述

為使討論簡單易懂，我將問題稍作簡化，去掉諸多的背景。

從前有一個皇帝，他有50個妃子，這些妃子很沒有天理的給他生了100,000個兒子，於是，皇帝很苦惱，海量的兒子很難管理，而且，他想知道每個妃子給他生了多少個兒子，從而論功行賞，這很難辦。於是，皇帝請了一個程式設計師幫他編了一個程式，用資料庫來儲存所有的兒子的資訊，這樣就可以用程式來統計和管理啦。

資料庫的結構如下：

id	皇子的唯一編號
mother	皇子母親的唯一編號

皇帝把妃子分成了兩個等級，天宮娘娘(編號小於25)和地宮娘娘(編號大於等於25)，他想知道天宮娘娘們和地宮娘娘們的生育能力孰強孰弱。於是，程式設計師開始寫SQL Query了。

方法1：使用GROUP BY

SQL Query

SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

執行結果

count(*)
50029
49971

在100,000行資料上的執行時間：0.0335 秒

分析

這種GROUP BY方法的最大問題在於：無法區分所得到的結果。這兩個數字哪一個是天宮娘娘們所生的皇子數，哪一個是地宮娘娘們所生的皇子數呢？不知道。所以，儘管它統計出了總數，但是沒有什麼意義。

因此，為了區分統計結果，必須要把條件 mother > 24 也作為一個欄位在結果集中作為一個欄位體現出來，修改後的sql如下：

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

執行結果

number  type
50029   0
49971   1

條件表示式作為欄位時，該欄位的值就是該條件表示式的值，因此，對應我們的例子，type = 1 也就是表示 mother > 24 的值為1，因此，第二行中的數字代表地宮娘娘們所生的皇子數。

經過修改後，我們看出，天宮娘娘們略勝一籌。

優缺點

缺點是顯而易見的，由於使用了條件表示式作為分組依據，它只能做二元的劃分，對於要分成多類進行統計的情況不能夠勝任。比如要分別統計1~10號、11~24號，25號~50號妃子的產子數，就無法實現了。

另外，由於使用了GROUP BY，因此涉及到排序，執行時間上要更長。

我暫時沒有發現這種方法的優點。

方法2：使用巢狀的SELECT

使用巢狀的SELECT也可以達到目的，在每個SELECT子句中統計一個條件下的資料，然後用一個主SELECT把這些統計資料整合起來。

SQL Query

SELECT 
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`, 
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

執行結果

digong  tiangong
49971   50029

在100,000行資料上的執行時間：0.0216 秒

分析

這種巢狀SELECT的方法非常直觀，就是分別統計各個條件下的數值，最後進行彙總，通俗易懂，跟自然語言沒啥區別了。

優缺點

優點就是直觀，而且速度也比GROUP BY要快。雖然是3條SELECT語句，看起來比GROUP BY的方案多了2條語句，但是它不涉及到排序，這就節省了很多時間。

缺點可能就是語句稍多，對語句數量有潔癖的同學可能會比較不舒服。

方法3：使用CASE WHEN

CASE WHEN語句的功能很強大，可以定義靈活的查詢條件，很適合進行分類統計。

SQL Query

SELECT 
    COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`, 
    COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`
FROM prince

執行結果

digong  tiangong
49971   50029

在100,000行資料上的執行時間：0.02365825 秒

分析

此方法的關鍵在於

COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) 

這裡的COUNT和CASE WHEN聯合使用，做到了分類計數。先使用CASE WHEN，當滿足條件時，將欄位值設定為 1， 不滿足條件時，將欄位值設定為NULL，接著COUNT函式僅對非NULL欄位進行計數，於是，問題解決。

優缺點

優點嘛，此方法也不涉及到排序，因此執行時間上與方法2相當，SELECT語句減少到了 1 條。

缺點就是語句比較長，對語句長度有潔癖的同學可能會比較不舒服。

總結

對於確定分類的按條件計數，可以儘量不用GROUP BY，從而避免排序動作，加速Query的執行。

如果需要根據某個欄位的值進行分類，而該欄位的值是可變的，比如皇帝要統計每一個妃子的產子數，而他可能不停的再娶很多妃子，這種情況下，使用方法2和方法3就不太靈光了，還是使用一個GROUP BY來得簡單便捷。