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

youzhibing2904發表於2019-07-29

前言

　　歷史考試選擇題：黃花崗起義第一槍誰開的? 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進階教程》

SQL CASE 表示式
2022-03-22
SQL
瞭解GaussDB SQL中CASE表示式
2024-05-13
SQL
GaussDB SQL基本語法示例-CASE表示式
2024-11-25
SQL
神奇的 SQL 之效能優化 → 讓 SQL 飛起來
2021-01-05
SQL優化
神奇的 SQL 之 HAVING → 容易被輕視的主角
2020-12-24
SQL
sql裡的正規表示式
2020-11-16
SQL
SQL正規表示式
2024-03-06
SQL
神奇的 SQL 之別樣的寫法 → 行行比較
2021-08-13
SQL
神奇的 SQL 之擦肩而過 → 真的用到索引了嗎
2020-12-28
SQL索引
《MySQL 入門教程》第 13 篇 CASE 表示式與控制流函式
2022-02-06
MySql函式
Django筆記二十三之case、when操作條件表示式搜尋、更新等操作
2023-04-13
Django筆記
稀裡糊塗系列之list函式妙用
2018-10-25
函式
SQL Server CASE WHEN ... THEN ... ELSE ... END
2024-07-25
SQLServer
sql case when, Exist ,group by ,聚合
2024-03-15
SQL
Oracle case when改寫SQL
2020-05-23
OracleSQL
強大的拉姆表示式轉Sql 類庫 - SqlSugar 隱藏功能之Lambda
2020-12-06
SqlSugar
Java之lambda表示式
2021-03-27
Java
帶你瞭解GaussDB SQL中的BOOLEAN表示式
2024-05-10
SQLBoolean
神奇的 SQL ，高階處理之 Window Functions → 打破我們的侷限！
2023-12-18
SQLFunction
SQL 正規表示式 `(user_log_acct)?+.+`
2020-04-06
SQL
MaxCompute SQL 使用正規表示式選列
2019-04-16
SQL
[20180928]避免表示式在sql語句中.txt
2018-09-28
SQL
c++之lambda表示式
2020-11-09
C++
python之正規表示式
2018-08-11
Python
Linux之grep中的正規表示式
2020-12-24
Linux
03 shell程式設計之case語句與函式
2018-06-10
程式設計函式
正規表示式之難點
2019-07-30
JDK1.8之lambda表示式
2020-06-24
JDK
JDK1.8 之Lambda表示式
2019-04-02
JDK
java正規表示式之 group
2018-07-19
Java
Python3之lambda表示式
2018-09-02
Python
「Java8系列」神奇的函式式介面
2019-07-18
Java函式
JDK1.8的新特性之Lambda表示式
2019-08-14
JDK
excel妙用之VLOOKUP函式
2024-03-16
Excel函式
PHP 陣列函式妙用
2018-10-31
PHP陣列函式
php之正規表示式函式總結
2019-02-16
PHP函式
DSC：數倉SQL指令碼遷移的神奇工具
2023-03-14
SQL指令碼
天天寫 SQL，這些神奇的特性你知道嗎？
2022-09-16
SQL

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

前言

CASE表示式 之概念

CASE表示式 之妙用

行轉列

轉換統計

條件分支

CHECK 約束

總結

參考

相關文章

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

CASE表示式之概念

CASE表示式之妙用

　　行轉列

　　轉換統計

　　條件分支

　　CHECK 約束