神奇的 SQL 之 CASE表達式，妙用多多 ！
前言
歷史考試選擇題：黃花崗起義第一槍誰開的? A宋教仁 B孫中山 C黃興 D徐錫麟，考生選C。
又看第二題：黃花崗起義第二槍誰開的? 考生傻了，就選了個B。
接著看第三題：黃花崗起義中，第三槍誰開的? 考生瘋了，胡亂選了A。
考試出來就去找出卷老師。老師拿出課本說：黃興連開三槍，揭開了黃花崗起義的序幕。考生：......
相信大家都用過CASE表達式，尤其是做一些統(tǒng)計功能的時候，用的特別多，可真要說什么是 CASE表達式，我估計還真沒幾個人能清楚的表述出來。
CASE表達式和 “2+1” 或者 “120/3” 這樣的表達式一樣，是一種進行運算的功能，正如CASE（情況）這個詞的含義一樣，用于區(qū)分情況，在有條件分歧的時候使用它。
CASE表達式是從 SQL-92 標準開始被引入的，可能因為它是相對較新的技術(shù)，所以盡管使用起來非常便利，但其真正的價值卻并不怎么為人所知。很多人不用它，或者用它的簡略版函數(shù)，例如 DECODE（Oracle）、IF（MySQL）等。然而，CASE表達式也許是 SQL-92 標準里加入的最有用的特性，如果能用好它，那么 SQL 能解決的問題就會更廣泛，寫法也會更加漂亮，而且，因為 CASE表達式 是不依賴于具體數(shù)據(jù)庫的技術(shù)，所以可以提高 SQL 代碼的可移植性。
基本格式如下
--?簡單?CASE表達式
CASE?列(或表達式)
?????WHEN?<匹配值1>?THEN?<表達式>
?????WHEN?<匹配值2>?THEN?<表達式>
?????......
?????ELSE?<表達式>
END
--?搜索?CASE表達式
CASE?WHEN?<判斷表達式>?THEN?<表達式>
?????WHEN?<判斷表達式>?THEN?<表達式>
?????WHEN?<判斷表達式>?THEN?<表達式>
?????......
?????ELSE?<表達式>
END
--?簡單?CASE表達式?示例
CASE?sex
????WHEN?'1'?THEN?'男'
????WHEN?'2'?THEN?'女'
????ELSE?'其他'?
END
--?搜索CASE表達式?示例
CASE?WHEN?sex?=?'1'?THEN?'男'
?????WHEN?sex?=?'2'?THEN?'女'
?????ELSE?'其他'?
END
CASE表達式 之妙用
行轉(zhuǎn)列
CREATE?TABLE?t_customer_credit?(
????id?INT(11)?UNSIGNED?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
????login_name?VARCHAR(50)?NOT?NULL?COMMENT?'登錄名',
????credit_type?TINYINT(1)?NOT?NULL?COMMENT?'額度類型，1:自由資金,2:凍結(jié)資金,3:優(yōu)惠',
????amount?DECIMAL(22,6)?NOT?NULL?DEFAULT?'0.00000'?COMMENT?'額度值',
????create_by?VARCHAR(50)?NOT?NULL?COMMENT?'創(chuàng)建者',
????create_time?DATETIME?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'創(chuàng)建時間',
????update_time?DATETIME?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'創(chuàng)建時間',
????update_by?VARCHAR(50)?NOT?NULL?COMMENT?'修改者',
??PRIMARY?KEY?(id)
);
INSERT?INTO?`t_customer_credit`?VALUES?(1,?'zhangsan',?1,?550.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-8?20:21:05',?'system');
INSERT?INTO?`t_customer_credit`?VALUES?(2,?'zhangsan',?2,?0.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-7?11:30:09',?'system');
INSERT?INTO?`t_customer_credit`?VALUES?(3,?'zhangsan',?3,?0.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-7?11:30:09',?'system');
INSERT?INTO?`t_customer_credit`?VALUES?(4,?'lisi',?1,?0.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-7?11:30:09',?'system');
INSERT?INTO?`t_customer_credit`?VALUES?(5,?'lisi',?2,?0.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-7?11:30:09',?'system');
INSERT?INTO?`t_customer_credit`?VALUES?(6,?'lisi',?3,?0.000000,?'system',?'2019-7-7?11:30:09',?'2019-7-7?11:30:09',?'system');
如果我們要一行顯示用戶的三個額度，而不是 3 條記錄顯示 3 個額度，我們應(yīng)該怎么做，方式有很多種，這里提供如下 3 種
--?1、最容易想到的IF，不具備移植性，不推薦
SELECT?login_name,
????MAX(IF(credit_type=1,?amount,?0))?freeAmount,
????MAX(IF(credit_type=2,?amount,?0))?freezeAmount,
????MAX(IF(credit_type=3,?amount,?0))?promotionAmount
FROM?t_customer_credit?GROUP?BY?login_name;
--?2、CASE表達式，標準的?SQL?規(guī)范，具備移植性，推薦使用
SELECT?login_name,
????MAX(CASE?WHEN?credit_type?=?1?THEN?amount?ELSE?0?END)?freeAmount,
????MAX(CASE?WHEN?credit_type?=?2?THEN?amount?ELSE?0?END)?freezeAmount,
????MAX(CASE?WHEN?credit_type?=?3?THEN?amount?ELSE?0?END)?promotionAmount
FROM?t_customer_credit?GROUP?BY?login_name;
--?3、自連接，數(shù)據(jù)量大的情況下，結(jié)合索引，效率不錯，具備移植性
SELECT
????a.login_name,a.amount?freeAmount,
????b.amount?freezeAmount,
????c.amount?promotionAmount
FROM?(
????SELECT?login_name,?amount?FROM?t_customer_credit?WHERE?credit_type?=?1
)a
LEFT?JOIN?t_customer_credit?b?ON?a.login_name?=?b.login_name?AND?b.credit_type?=?2
LEFT?JOIN?t_customer_credit?c?ON?a.login_name?=?c.login_name?AND?c.credit_type?=?3;
無論是 IF 還是 CASE表達式，都結(jié)合了 GROUP BY 與聚合函數(shù)，效率是個問題，而自連接是效率最高的，不管在不在 login_name 上加索引
轉(zhuǎn)換統(tǒng)計
將已有編號方式轉(zhuǎn)換為新的方式并統(tǒng)計，在進行非定制化統(tǒng)計時，我們經(jīng)常會遇到將已有編號方式轉(zhuǎn)換為另外一種便于分析的方式并進行統(tǒng)計的需求。假設(shè)我們有如下表
DROP?TABLE?t_province_population;
CREATE?TABLE?t_province_population?(
??id?tinyint(2)?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??province_name?varchar(50)?NOT?NULL?COMMENT?'省份名',
??sex?tinyint(1)?NOT?NULL?COMMENT?'性別，1:男,2:女',
??population?int(11)?NOT?NULL?COMMENT?'人口數(shù)',
??PRIMARY?KEY?(id)
);
INSERT?INTO?t_province_population(province_name,sex,population)
VALUES
("黑龍江",?1?,20),
("黑龍江",?2?,18),
("內(nèi)蒙古",?1?,7),
("內(nèi)蒙古",?2?,8),
("海南",?1?,20),
("海南",?2?,22),
("西藏",?1?,8),
("西藏",?2?,7),
("浙江",?1?,35),
("浙江",?2?,35),
("臺灣",?1?,26),
("臺灣",?2?,23),
("河南",?1?,40),
("河南",?2?,38),
("湖北",?1?,27),
("湖北",?2?,24);
SELECT?*?FROM?t_province_population;
我們需要按各個省所在的位置，統(tǒng)計出東南西北中，各個區(qū)域內(nèi)的人口數(shù)量
東：浙江、臺灣，西：西藏，南：海南，北：黑龍江、內(nèi)蒙古，中：湖北、河南
可能有人覺得這個表設(shè)計的不合理，應(yīng)該在設(shè)計之初就應(yīng)該多加一個區(qū)域字段（district）來標明各省所屬區(qū)域。最好的做法確實是這樣，但這得需要我們在設(shè)計之初的時候能考慮得到，或者有這樣的需求，假設(shè)我們設(shè)計之初沒有這樣的需求，而我們也沒考慮到，那么有沒有什么辦法來實現(xiàn)了？我們可以這樣來寫 SQL
--?通用寫法，適用于多種數(shù)據(jù)庫
SELECT?CASE?province_name
????WHEN?'浙江'?THEN?'東'
????WHEN?'臺灣'?THEN?'東'
????WHEN?'海南'?THEN?'南'
????WHEN?'西藏'?THEN?'西'
????WHEN?'黑龍江'?THEN?'北'
????WHEN?'內(nèi)蒙古'?THEN?'北'
????WHEN?'河南'?THEN?'中'
????WHEN?'湖北'?THEN?'種'
????ELSE?'其他'?END?district,
????SUM(population)?populations
FROM?t_province_population
GROUP?BY?CASE?province_name
????WHEN?'浙江'?THEN?'東'
????WHEN?'臺灣'?THEN?'東'
????WHEN?'海南'?THEN?'南'
????WHEN?'西藏'?THEN?'西'
????WHEN?'黑龍江'?THEN?'北'
????WHEN?'內(nèi)蒙古'?THEN?'北'
????WHEN?'河南'?THEN?'中'
????WHEN?'湖北'?THEN?'中'
????ELSE?'其他'?END;
--?MySQL支持寫法，移植性差
SELECT?CASE?province_name
????WHEN?'浙江'?THEN?'東'
????WHEN?'臺灣'?THEN?'東'
????WHEN?'海南'?THEN?'南'
????WHEN?'西藏'?THEN?'西'
????WHEN?'黑龍江'?THEN?'北'
????WHEN?'內(nèi)蒙古'?THEN?'北'
????WHEN?'河南'?THEN?'中'
????WHEN?'湖北'?THEN?'中'
????ELSE?'其他'?END?district,
????SUM(population)?populations
FROM?t_province_population
GROUP?BY?district;
結(jié)果如下
假設(shè)我們需要對各個省份做一個人口數(shù)級別的統(tǒng)計，統(tǒng)計出各個級別的數(shù)量
level_1：population < 20，level_2：20 <= population < 50 ，level_3：50 <= population < 70?，level_4：>= 70；統(tǒng)計出 level_1 ~ level_4 的數(shù)量各有多少
SQL 與執(zhí)行結(jié)果如下
SELECT?
????CASE?WHEN?population?20?THEN?'level_1'
????????WHEN?population?>=?20?AND?population?50?THEN?'level_2'
????????WHEN?population?>=?50?AND?population?70?THEN?'level_3'
????????WHEN?population?>=?70?THEN?'level_4'
????????ELSE?NULL?
????END?pop_level,
????COUNT(*)?cnt
FROM?(
????SELECT?province_name,SUM(population)?population?FROM?t_province_population?GROUP?BY?province_name
)a
GROUP?BY?
????CASE?WHEN?population?20?THEN?'level_1'
????????WHEN?population?>=?20?AND?population?50?THEN?'level_2'
????????WHEN?population?>=?50?AND?population?70?THEN?'level_3'
????????WHEN?population?>=?70?THEN?'level_4'
????????ELSE?NULL?
????END;
這種轉(zhuǎn)換統(tǒng)計還是比較常用的，重點就是 GROUP BY 子句的寫法。
條件分支
SELECT 條件分支
還是以上面的?t_province_population 為例，如果我們想要直觀的知道各個省份的男、女?dāng)?shù)量情況，類似如下
我們要怎么寫 SQL？有如下兩種方法
--?1、CASE表達式?集合?GROUP?BY
SELECT?province_name,
????SUM(CASE?WHEN?sex?=?1?THEN?population?ELSE?0?END)?c,
????SUM(CASE?WHEN?sex?=?2?THEN?population?ELSE?0?END)?f_pops
FROM?t_province_population
GROUP?BY?province_name;
--?2、自關(guān)聯(lián)
SELECT?t.province_name,?t.population?m_pops,?a.population?f_pops
FROM?t_province_population?t
LEFT?JOIN?t_province_population?a
ON?t.province_name?=?a.province_name
WHERE?t.sex?=?1?AND?a.sex?=?2;
其實就是行轉(zhuǎn)列，行轉(zhuǎn)列更容易懂
UPDATE 條件分支
我們有一張薪資表，如下
CREATE?TABLE?t_user_salaries(
??id?int(11)?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??name?varchar(50)?NOT?NULL?COMMENT?'姓名',
????sex?tinyint(1)?NOT?NULL?COMMENT?'性別，1:男,2:女',
??salary?int(11)?NOT?NULL?COMMENT?'薪資',
??PRIMARY?KEY?(id)
);
INSERT?INTO?t_user_salaries(name,?sex,salary)?VALUES
("張三",?1,?30000),
("李四",?1,?27000),
("王五",?1,?22000),
("菲菲",?2,?24000),
("趙六",?1,?29000);
SELECT?*?FROM?t_user_salaries;
假設(shè)現(xiàn)在需要根據(jù)以下條件對該表的數(shù)據(jù)進行更新：1、對當(dāng)前工資為 30000 元以上的員工，降薪 10%，2、對當(dāng)前工資為 25000 元以上且不滿 28000 元的員工，加薪 20%。調(diào)整之后的薪資如下所示
乍一看，分別執(zhí)行下面兩個 UPDATE 操作好像就可以做到，但是我們執(zhí)行下看看結(jié)果
--?條件1
UPDATE?t_user_salaries
SET?salary?=?salary?*?0.9
WHERE?salary?>=?30000;
--?條件2
UPDATE?t_user_salaries
SET?salary?=?salary?*?1.2
WHERE?salary?>=?25000?AND?salary?28000;
我們發(fā)現(xiàn)張三的薪資不降反升了！這是因為執(zhí)行 條件1的SQL后，張三的薪資又滿足條件2了，所以又更新了一遍，導(dǎo)致他的薪資變多了，有人可能會說，把條件1和條件2的SQL換下順序不就好了嗎，我們來試試
--?條件2
UPDATE?t_user_salaries
SET?salary?=?salary?*?1.2
WHERE?salary?>=?25000?AND?salary?28000;
--?條件1
UPDATE?t_user_salaries
SET?salary?=?salary?*?0.9
WHERE?salary?>=?30000;
張三的薪資是降對了，可李四的薪資卻漲錯了！這是因為李四的薪資滿足條件2，升了 20% 之后又滿足條件1，又降了 10%。難道就沒有就沒有正確的方式了？我們來看看這個 SQL
UPDATE?t_user_salaries?SET?salary?=?
????CASE?WHEN?salary?>=?30000?THEN?salary?*?0.9
????????????WHEN?salary?>=?25000?AND?salary?28000?THEN?salary?*?1.2
????????????ELSE?salary
????END;
SELECT?*?FROM?t_user_salaries;
完美不？特別完美，這個技巧的應(yīng)用范圍很廣，值得我們掌握
CHECK 約束
注意：CHECK 是標準的 SQL，但是 MySQL 卻沒有實現(xiàn)它，所以 CHECK 在 MySQL 中是不起作用的！
回到我們的薪資表，假設(shè)某個公司有這樣一個無理的規(guī)定：女性員工的工資不得高于50000，我們?nèi)绻麑崿F(xiàn)它？?方式有兩種：1、代碼層面控制 、2、數(shù)據(jù)庫表加約束。
代碼層面控制就不多說了，這我們平時最能想到的，實際也是用的最多的；那從表約束，我們該如何實現(xiàn)了，像這樣嗎？
--?創(chuàng)建表的時候增加約束
CREATE?TABLE?t_user_salaries_check(
??name?varchar(50)?NOT?NULL?COMMENT?'姓名',
????sex?tinyint(1)?NOT?NULL?COMMENT?'性別，1:男,2:女',
??salary?int(11)?NOT?NULL?COMMENT?'薪資',
????CONSTRAINT?chk_sex_salary?CHECK?(sex=2?AND?salary?<=?50000)
);
--?若t_user_salaries_check已創(chuàng)建，則補充上約束
ALTER?TABLE?t_user_salaries_check
ADD?CONSTRAINT?chk_sex_salary?CHECK?(sex=2?AND?salary?<=?50000);
這么實現(xiàn)你會發(fā)現(xiàn)公司的男同事都會提著刀來找你了，因為沒有他們的薪資，這個約束會導(dǎo)致錄入不了男性的薪資！因為我們的約束是：sex=2 AND salary < = 50000 表示 “是女性，并且薪資不能高于50000”，而不是：“如果是女性，薪資不高于50000”。正確的約束條件應(yīng)該這么寫
--?創(chuàng)建表的時候增加約束
CREATE?TABLE?t_user_salaries_check(
??name?varchar(50)?NOT?NULL?COMMENT?'姓名',
????sex?tinyint(1)?NOT?NULL?COMMENT?'性別，1:男,2:女',
??salary?int(11)?NOT?NULL?COMMENT?'薪資',
??PRIMARY?KEY?(id),
????CONSTRAINT?chk_sex_salary?CHECK(
????????CASE?WHEN?sex?=?2?THEN?
????????????????????????CASE?WHEN?salary?<=?50000?THEN?1?
????????????????????????????????ELSE?0?
????????????????????????END
????????????????ELSE?1?
????????END?=?1?)
);
--?若t_user_salaries_check已創(chuàng)建，則補充上約束
ALTER?TABLE?t_user_salaries_check
ADD?CONSTRAINT?chk_sex_salary?CHECK(
????CASE?WHEN?sex?=?2?THEN?
????????????????????????CASE?WHEN?salary?<=?50000?THEN?1?
????????????????????????????????ELSE?0?
????????????????????????END
????????????????ELSE?1?
????????END?=?1?
);
CASE表達式還有很多其他的用處，強大的不得了，而且高度靈活；用好它，能讓我們寫出更加契合的 SQL。
總結(jié)
1、CASE表達式 是支撐 SQL 聲明式編程的根基之一，也是靈活運用 SQL 時不可或缺的基礎(chǔ)技能。作為表達式，CASE 表達式在執(zhí)行時會被判定為一個固定值，因此它可以寫在聚合函數(shù)內(nèi)部；也正因為它是表達式，所以還可以寫在SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。簡單點說，在能寫列名和常量的地方，通常都可以寫 CASE 表達式
2、寫 CASE表達式 的注意點
????a、各個分支返回的數(shù)據(jù)類型要一致
????b、養(yǎng)成寫 ELSE 的好習(xí)慣
????c、不要忘了寫 END
參考
《SQL基礎(chǔ)教程》《SQL進階教程》
youzhibing2904
https://www.cnblogs.com/youzhibing/p/11240536.html
我是岳哥，最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實例講解和批注等等，非常通俗易懂，方便大家跟著一起來實操。
有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進群，帶你進入高手如云的交流群。
推薦閱讀
