case when 作為條件_【SQL】區間（條件）分組統計

簡介

很多時候，我們都使用group by 進行分組，count(*)進行統計，兩者結合可以進行聚合統計。

假設我們有這樣一張煤礦資料庫表

table name: coalmine
columns: id(煤礦ID, bigint), prod_status(生産狀态,varchar), prod_capacity(産能,decimal)
           

需求：統計各生産狀态的煤礦數量

學過SQL的人一眼就看出來，這是一個非常基礎的問題。我們隻需要按照prod_status進行分組進行聚合統計即可。大緻可以寫如下的sql：

select prod_status as name, count(*) as num from coalmine group by name;
           

可以得到如下的輸出：

name | num
停産  377
停建  360
關閉  31
準備  1
在建  89
正在複産    3
生産  463
生産/在建   15
生産/試運轉  12
試運轉 1
           

非常完美，我們得到了我們想要的資料。

但是現在有了新的需求：統計産能在30以下，30~90，90以上的煤礦數量有多少。現在我們遇到了難題，因為産能字段(prod_capacity)是一個數值，同時統計的依據是一個區間，我們不能單純的将其作為group by的對象進行操作。

select prod_capacity as name, count(*) as num from coalmine group by name;
           

這樣做的結果，隻是按數值進行分組統計。

那麼，該怎麼辦呢？

區間統計（解法一）

有點基礎的讀者不難看出，我們可以使用mysql關鍵字

sum

以及

if

進行操作，大緻可以寫出如下的SQL。

select
    sum(if(c.prod_capacity is null or c.prod_capacity < 30, 1, 0)) as less30,
    sum(if(c.prod_capacity >= 30 and c.prod_capacity <= 90, 1, 0)) as between39,
    sum(if(c.prod_capacity > 90, 1 , 0)) as gather90
from coalmine c inner join enterprise e on c.enterprise_id = e.id;
           

c.prod_capacity is null 可以認為prod_capacity字段為空時，認為煤礦的産能低于30.

sum

以及

if

的使用方法可參閱網上教程。執行完畢後，我們可以得到如下的結果：

less30 | between39 | than90
 1033   330          25
           

看起來似乎很美好，隻不過沒有使用分組排序稍有欠缺，導緻最終結果是以一行的方式呈現，這回導緻我們在應用程式裡面進行實體映射（例如mybatis）時，隻能使用扁平結構進行對應（例如Map），這和統一的分組映射實體出現沖突。當然對于解決問題的結果來說這是無傷大雅的，最終我們還會講最完美的解法，在此之前，我們先看另一種解決方案。

區間統計（解法二）

mysql有衆多函數可以幫助我們完成各種各樣的任務，隻要我們仔細研究，很多備援的SQL可以簡化的漂亮，關于區間統計，其實還有專門的處理函數，他們分别是

interval

以及

ele

。我們來看看他們的用法：

INTERVAL(N,N1,N2,N3,...) INTERVAL()函數進行比較清單(N1，N2，N3等等)中的N值。該函數如果N<N1傳回0，如果N<N2傳回1，如果N<N3傳回2 等等。如果N為NULL，它将傳回-1。清單值必須是N1<N2<N3的形式才能正常工作。

ELT(N,str1,str2,str3,...) 如果N= 1，傳回str1，如果N= 2，傳回str2，等等。如果N小于1或大于參數個數，傳回NULL。ELT()是FIELD()反運算。
           

基于此，我們可以寫出更漂亮的SQL

select elt(interval(c.prod_capacity,0,30,90, 100000), 'less30', 'between39', 'than90') as name, count(*)
from coalmine c group by name;
           

但顯然，查詢的結果受限非常之大，interval是半區間方式，（即大于等于前者小于後者），這樣會導緻運用場景非常之有限。當然可以通過其他方式進行優化，但是已經如使用sum、if方式來得友善靈活。

但前者也有問題，就是查詢的結果并不是多條記錄展示，這樣在很多業務系統中，進行bean映射的時候，隻能采取hashmap方式進行結果映射。顯然其原理還是分組統計，我們希望結果是以多行的形式展示。那麼，該如何辦到呢？

區間統計（解法三）

可以看到，既然分組的邏輯是一種

if else

形式的，我們可不可以在mysql裡找到這種邏輯的關鍵字呢？顯然是有的，那便是

case

語句。以下是其官方文檔:

Syntax:

CASE value WHEN [compare_value] THEN result 
[WHEN [compare_value] THEN result ...] 
[ELSE result] 
END

或者

CASE WHEN [condition] THEN result 
[WHEN [condition] 
THEN result ...] 
[ELSE result] 
END
           

金風玉露一相逢，這便是我們要的東西，仔細琢磨一番，可以寫出如下的SQL

select 
    case 
      when c.prod_capacity is null or c.prod_capacity < 30 then 'less30' 
      when c.prod_capacity >= 30 and c.prod_capacity <=90 then 'less39'
      when c.prod_capacity > 90 then 'than90'
    end 
    as name, 
    count(*) as num
from coalmine c
group by name;
           

傳回結果如下所示：

name | num
less30  1033
less39  330
than90  25
           

結語

可以看到，使用case關鍵字不僅得到了我們想要的結果形式，同時他提供了更靈活的處理邏輯，不論是區間分組亦或是其他的非正常方式，我們都可以定義自己的處理邏輯，将業務上需要歸為一組的資料輸出(then)為同樣的值，然後進行分組。

當然，也許還有更完美的解決方案，不知君是否有所考慮呢？歡迎讨論。