神奇的 SQL 之 CASE 表达式 → 这么妖娆，不来看看吗

开心一刻

历史考试选择题：黄花岗起义第一枪谁开的? A 宋教仁 ，B 孙中山， C 黄兴， D 徐锡麟 考生选 C 又看第二题：黄花岗起义第二枪谁开的? 考生懵了，就选了个B 接着看第三题：黄花岗起义中，第三枪谁开的? 考生疯了，胡乱选了A。 考试出来就去找出卷老师 老师拿出课本说：黄兴连开三枪，揭开了黄花岗起义的序幕。 考生：......，这老师怕是个智障吧

概念

相信大家都用过 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表达式 的妙用，带你们看看她妖娆的多面

行转列

SQL 行转列，大家都明白这个问题吧，就是将多行数据转换为单行数据，还是不懂？ 直接上案例，假设我们有如下表，以及如下数据

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');

说明下这个表，客户额度表 t_customer_credit 有三种额度，分 3 条记录分别记录同个客户的三种额度 问：在一行显示同个客户的三个额度，SQL 该如何写？

SQL 函数

这种方式往往是大家最容易想到的，比如 MySQL 的 IF、Oracle 的 DECODE

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;

这种实现跟数据库有强绑定关系，不具备移植性，不推荐！

CASE 表达式

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;

CASE 表达式 是标准的 SQL 规范，主流关系型数据库都支持，具备移植性，推荐使用！

自连接

数据量大的情况下，SQL函数 和 CASE表达式 的方式都会有效率问题

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;

这种实现结合合适的索引，效率会不错，也具备移植性，推荐使用！

你们发现没有，综合下来，自连接 才是最终归宿呀，跟 CASE 可以没关系呀

转换统计

假设我们有表 t_province_population 以及数据，如下

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);

区域人口统计

将这些省份分成 5 个区域：东、西、南、北、中，

东：浙江、台湾
西：西藏
南：海南
北：黑龙江、内蒙古
中：湖北、河南

问：统计各个区域内的人口数量，SQL 该如何写？ 你们第一反应是不是觉得这个表设计不合理：不应该增加一个 区域 字段来标明各省所属区域吗？ 于是你们带着这个疑问去查是谁设计的这个表：卧槽，竟然是你自己设计的！ 于是你们安慰自己：当初设计的时候，没这个需求，计划赶不上变化嘛 所以你们还是得硬着头皮解决这个需求，此时 CASE 就派上用场了

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;

但这种写法仅限于 MySQL，不具备移植性

省份人口级别统计

还是表：t_province_population 按人口数对省份划分级别

level_1：population < 20
level_2：20 <= population < 50
level_3：50 <= population < 70
level_4：>= 70

问：统计 level_1 ~ level_4 的省份各有多少，SQL 该如何写？ 此刻，估计你们的心态已经崩了

直接上 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;

执行结果如下

这种转换统计还是比较常用的，重点就是 GROUP BY 子句的写法

条件分支

分两种，SELECT 分支 和 update 分支 大家先别怕，这个比较简单，我们一个一个来看

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;

大家仔细看下如上 SQL，然后再回到上面 行转列 的 SQL 对比下来，你发现了什么？ 没错，就是同个东西！ 说明你们还没晕，那就继续往下看

UPDATE 分支

这个就有点意思了，可得看仔细了哦 假设我们有一张薪资表 t_user_salaries，如下

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);

假设现在需要根据以下条件对该表的数据进行更新

1、对当前工资为 30000 元以上的员工，降薪 10%
2、对当前工资为 25000 元以上且不满 28000 元的员工，加薪 20%

调整之后的薪资如下所示

该 SQL 该如何写？ 这个对于你们来说简单吧，我直接写出你们所想

-- 条件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;

这是你们所想吧，我没写错吧，来看下执行结果

结果不对呀，张三的薪资不降反升了！ 这是因为执行 条件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;

张三的薪资是降对了，可李四的薪资却涨错了！ 这是因为李四的薪资满足 条件2，升了 20% 之后又满足 条件1，又降了 10% 此时，你们脑海中是不是响起了这首歌：《被伤过的心还可以爱谁》

难道就无解了吗，并不是，此时就需要 妖娆 的 CASE 了

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;

就问你们，case 妖不妖娆？

你以为 case 就 妖 完了？ 并没有，我们继续往下看

CHECK 约束

先说明一下：CHECK 是标准的 SQL，但是 MySQL 却没有实现它，所以 CHECK 在 MySQL 中是不起作用的！ 回到我们的薪资表 t_user_salaries 假设某个公司有这样一个无理的规定：女性员工的工资不得高于 50000 数据库层面，该如何实现？ 方式有很多种，check 约束就是其中之一

-- 创建表的时候增加约束
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);

如果按如上实现，你会发现下次发工资的时候，公司的男同事都会提着刀来找你了 约束 sex=2 AND salary < = 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 
);

CASE 表达式还有很多其他的用处，强大的不得了，而且高度灵活 用好它，能让我们写出更加契合的 SQL

总结

1、CASE 表达式很重要     CASE 表达式是支撑 SQL 声明式编程的根基之一，也是灵活运用 SQL 时不可或缺的基础技能     作为表达式，CASE 表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部，也正因为它是表达式，所以还可以写在 SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句 里     简单点说，在能写列名和常量的地方，通常都可以写 CASE 表达式 2、写 CASE表达式注意点     各个分支返回的数据类型要一致     养成写 ELSE 的好习惯     不要忘了写 END 3、多条件时，用 OR 、AND 等谓词

参考

《SQL基础教程》 《SQL进阶教程》