神奇的 SQL 之 CASE表示式，妙用多多 ！

youzhibing2904發表於2019-07-29
SQL

前言

　　歷史考試選擇題：黃花崗起義第一槍誰開的? A宋教仁 B孫中山 C黃興 D徐錫麟，考生選C。

　　又看第二題：黃花崗起義第二槍誰開的? 考生傻了，就選了個B。

　　接著看第三題：黃花崗起義中，第三槍誰開的? 考生瘋了，胡亂選了A。

　　考試出來就去找出卷老師。老師拿出課本說：黃興連開三槍，揭開了黃花崗起義的序幕。考生：......

CASE表示式 之概念

　　相信大家都用過CASE表示式，尤其是做一些統計功能的時候，用的特別多，可真要說什麼是 CASE表示式，我估計還真沒幾個人能清楚的表述出來。CASE表示式和 “2+1” 或者 “120/3” 這樣的表示式一樣，是一種進行運算的功能，正如CASE（情況）這個詞的含義一樣，用於區分情況，在有條件分歧的時候使用它。CASE表示式是從 SQL-92 標準開始被引入的，可能因為它是相對較新的技術，所以儘管使用起來非常便利，但其真正的價值卻並不怎麼為人所知。很多人不用它，或者用它的簡略版函式，例如 DECODE（Oracle）、IF（MySQL）等。然而，CASE表示式也許是 SQL-92 標準里加入的最有用的特性，如果能用好它，那麼 SQL 能解決的問題就會更廣泛，寫法也會更加漂亮，而且，因為 CASE表示式 是不依賴於具體資料庫的技術，所以可以提高 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表示式 的 ELSE子句 可以省略，但推薦不要省略，省略了可能會出現我們意料之外的結果。END不能省，必須有。當 WHEN子句 為真時，CASE表示式 的真假值判斷就會中止，而剩餘的 WHEN子句會被忽略。為了避免引起不必要的混亂，使用 WHEN子句 時要注意條件的排他性。

　　簡單CASE表示式正如其名，寫法簡單，但能實現的功能比較有限。簡單CASE表示式能寫的條件，搜尋CASE表示式也能寫，所以基本上採用搜尋CASE表示式的寫法。

CASE表示式 之妙用

　　上面講了 CASE表示式 的理論知識，感覺不痛不癢，那麼接下來我們進入實戰篇，結合一些場景來看看 CASE表示式 的妙用

　　行轉列

　　　　可能我們用的更多的是 IF（MySQL）或 DECODE（Oracle），但這兩者都不是標準的 SQL，更推薦大家用 CASE表示式，移植性更高

　　　　假設我們有如下表，以及如下資料

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:凍結資金,3:優惠',
    amount DECIMAL(22,6) NOT NULL DEFAULT '0.00000' COMMENT '額度值',
    create_by VARCHAR(50) NOT NULL COMMENT '建立者',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '建立時間',
    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');
View Code

　　　　如果我們要一行顯示使用者的三個額度，而不是 3 條記錄顯示 3 個額度，我們應該怎麼做，方式有很多種，這裡提供如下 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 規範，具備移植性，推薦使用
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、自連線，資料量大的情況下，結合索引，效率不錯，具備移植性
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;
View Code

　　　　無論是 IF 還是 CASE表示式，都結合了 GROUP BY 與聚合函式，效率是個問題，而自連線是效率最高的，不管在不在 login_name 上加索引

　　轉換統計

　　　　將已有編號方式轉換為新的方式並統計，在進行非定製化統計時，我們經常會遇到將已有編號方式轉換為另外一種便於分析的方式並進行統計的需求。假設我們有如下表

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 '人口數',
  PRIMARY KEY (id)
);

INSERT INTO t_province_population(province_name,sex,population)
VALUES
("黑龍江", 1 ,20),
("黑龍江", 2 ,18),
("內蒙古", 1 ,7),
("內蒙古", 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;
View Code

　　　　我們需要按各個省所在的位置，統計出東南西北中，各個區域內的人口數量

　　　　　　東：浙江、臺灣，西：西藏，南：海南，北：黑龍江、內蒙古，中：湖北、河南

　　　　可能有人覺得這個表設計的不合理，應該在設計之初就應該多加一個區域欄位（district）來標明各省所屬區域。最好的做法確實是這樣，但這得需要我們在設計之初的時候能考慮得到，或者有這樣的需求，假設我們設計之初沒有這樣的需求，而我們也沒考慮到，那麼有沒有什麼辦法來實現了？ 我們可以這樣來寫 SQL

-- 通用寫法，適用於多種資料庫
SELECT CASE province_name
    WHEN '浙江' THEN '東'
    WHEN '臺灣' THEN '東'
    WHEN '海南' THEN '南'
    WHEN '西藏' THEN '西'
    WHEN '黑龍江' THEN '北'
    WHEN '內蒙古' 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 '內蒙古' THEN '北'
    WHEN '河南' THEN '中'
    WHEN '湖北' THEN '中'
    ELSE '其他' END;

-- MySQL支援寫法，移植性差
SELECT CASE province_name
    WHEN '浙江' THEN '東'
    WHEN '臺灣' THEN '東'
    WHEN '海南' THEN '南'
    WHEN '西藏' THEN '西'
    WHEN '黑龍江' THEN '北'
    WHEN '內蒙古' THEN '北'
    WHEN '河南' THEN '中'
    WHEN '湖北' THEN '中'
    ELSE '其他' END district,
    SUM(population) populations
FROM t_province_population
GROUP BY district;
View Code

　　　　結果如下

　　　　假設我們需要對各個省份做一個人口數級別的統計，統計出各個級別的數量

　　　　　　level_1：population < 20，level_2：20 <= population < 50 ，level_3：50 <= population < 70 ，level_4：>= 70；統計出 level_1 ~ level_4 的數量各有多少

　　　　SQL 與執行結果如下

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;
View Code

　　　　這種轉換統計還是比較常用的，重點就是 GROUP BY 子句的寫法。

　　條件分支

　　　　SELECT 條件分支

　　　　　　還是以上面的 t_province_population 為例，如果我們想要直觀的知道各個省份的男、女數量情況，類似如下

　　　　　　我們要怎麼寫 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、自關聯
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;
View Code

　　　　　　其實就是行轉列，行轉列更容易懂

　　　　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;
View Code

　　　　　　假設現在需要根據以下條件對該表的資料進行更新：1、對當前工資為 30000 元以上的員工，降薪 10%，2、對當前工資為 25000 元以上且不滿 28000 元的員工，加薪 20%。調整之後的薪資如下所示

　　　　　　乍一看，分別執行下面兩個 UPDATE 操作好像就可以做到，但是我們執行下看看結果

-- 條件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;
View Code

　　　　　　我們發現張三的薪資不降反升了！ 這是因為執行 條件1的SQL後，張三的薪資又滿足條件2了，所以又更新了一遍，導致他的薪資變多了，有人可能會說，把條件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;
View Code

　　　　　　張三的薪資是降對了，可李四的薪資卻漲錯了！這是因為李四的薪資滿足條件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;
View Code

　　　　　　完美不？ 特別完美，這個技巧的應用範圍很廣，值得我們掌握

　　CHECK 約束

　　　　注意：CHECK 是標準的 SQL，但是 MySQL 卻沒有實現它，所以 CHECK 在 MySQL 中是不起作用的！

　　　　回到我們的薪資表，假設某個公司有這樣一個無理的規定：女性員工的工資不得高於50000，我們如果實現它？ 方式有兩種：1、程式碼層面控制 、2、資料庫表加約束。

　　　　程式碼層面控制就不多說了，這我們平時最能想到的，實際也是用的最多的；那從表約束，我們該如何實現了，像這樣嗎？

-- 建立表的時候增加約束
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已建立，則補充上約束
ALTER TABLE t_user_salaries_check
ADD CONSTRAINT chk_sex_salary CHECK (sex=2 AND salary <= 50000);
View Code

　　　　這麼實現你會發現公司的男同事都會提著刀來找你了，因為沒有他們的薪資，這個約束會導致錄入不了男性的薪資！ 因為我們的約束是：sex=2 AND salary < = 50000 表示 “是女性，並且薪資不能高於50000”，而不是：“如果是女性，薪資不高於50000”。正確的約束條件應該這麼寫

-- 建立表的時候增加約束
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已建立，則補充上約束
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 
);
View Code

　　CASE表示式還有很多其他的用處，強大的不得了，而且高度靈活；用好它，能讓我們寫出更加契合的 SQL。

總結

　　1、CASE表示式 是支撐 SQL 宣告式程式設計的根基之一，也是靈活運用 SQL 時不可或缺的基礎技能。作為表示式，CASE 表示式在執行時會被判定為一個固定值，因此它可以寫在聚合函式內部；也正因為它是表示式，所以還可以寫在SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句裡。簡單點說，在能寫列名和常量的地方，通常都可以寫 CASE 表示式

　　2、寫 CASE表示式 的注意點

　　　　a、各個分支返回的資料型別要一致

　　　　b、養成寫 ELSE 的好習慣

　　　　c、不要忘了寫 END

參考

　　《SQL基礎教程》

　　《SQL進階教程》

相關文章