《数据库基础语法》7. 通过 CASE 表达式为 SQL 增加 IF-THEN-ELSE 逻辑，并且实现行列转换

楔子

上一节我们学习了 SQL 中常见的日期时间函数和类型转换函数，熟练使用各种函数可以让我们的数据处理和分析工作事半功倍。

本篇我们介绍一种为 SQL 语句增加逻辑处理功能的方法：CASE 表达式。

CASE 表达式

SQL 中的 CASE 表达式可以根据不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑功能。例如，根据员工的 KPI 计算相应的涨薪幅度，根据考试成绩评出优秀、良好、及格等。

CASE 表达式支持两种形式：简单 CASE 表达式  和  搜索 CASE 表达式。

简单 CASE 表达式

语法如下：

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

首先计算 expression 的值；然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个相等的值并返回对应的结果（result1，result2，…）；如果没有找到相等的值，返回 ELSE 中的默认结果；如果没有指定 ELSE，则返回 NULL 值。

SELECT age,
       CASE age
           WHEN 30 THEN '三十'
           WHEN 35 THEN '三十五'
           WHEN 40 THEN '四十'
           ELSE '不是三十、三十五、四十'
           END AS age1
FROM staff
WHERE age IN (30, 35, 40, 45)
LIMIT 10;
/*
30    三十
35    三十五
30    三十
45    不是三十、三十五、四十
45    不是三十、三十五、四十
45    不是三十、三十五、四十
40    四十
35    三十五
45    不是三十、三十五、四十
40    四十
 */

CASE 表达式的一个常见应用就是实现行列转换，举个栗子。

-- 创建成绩表 t_case，sname 为学生姓名，cname 为课程名称，score 为考试成绩
CREATE TABLE t_case(sname varchar(10), cname varchar(10), score int);

-- 插入测试数据
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '语文', 80);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '语文', 77);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '语文', 91);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '数学', 85);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '数学', 90);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '数学', 60);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '英语', 81);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '英语', 69);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '英语', 82);

执行以上语句创建 t_case 表并且插入数据，该表中的数据如下：

现在我们要把表变成这样的形式：

我们便可以通过 CASE 来解决：

SELECT sname,
       CASE cname WHEN '语文' THEN score ELSE 0 END AS "语文",
       CASE cname WHEN '数学' THEN score ELSE 0 END AS "数学",
       CASE cname WHEN '英语' THEN score ELSE 0 END AS "英语"
FROM t_case;

第一个 CASE 表达式用于获取学生的语文成绩，cname 等于 "语文" 就返回考试成绩，不是 "语文" 就记为 0 分。第二个和第三个 CASE 表达式分别用于获取数学和英语成绩，原理和第一个 CASE 表达式相同。该语句执行的结果如下：

已经有那么回事了，然后再聚合一下即可，聚合我们后面会说：

SELECT sname,
       SUM(CASE cname WHEN '语文' THEN score ELSE 0 END) AS "语文",
       SUM(CASE cname WHEN '数学' THEN score ELSE 0 END) AS "数学",
       SUM(CASE cname WHEN '英语' THEN score ELSE 0 END) AS "英语"
FROM t_case
GROUP BY sname;

我们看到已经实现了，因此 CASE 语句还是能做很多事情的，但是简单 CASE 表达式在进行判断的时候，使用的是等值比较（=），只能处理简单的逻辑。如果想要进行复杂的逻辑处理，例如根据考试成绩评出优秀、良好、及格等，就需要使用更加强大的搜索 CASE 表达式。

搜索 CASE 表达式

语法如下：

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE default_result]
END

按照顺序依次计算每个分支中的条件（condition1，condition2，…），找到第一个结果为真的分支并返回相应的结果（result1，result2，…）；如果没有任何条件为真，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。

所有的简单 CASE 表达式都可以替换为等价的搜索 CASE 表达式，我们刚才的例子就可以改写成如下：

SELECT age,
       CASE
           WHEN age = 30 THEN '三十'
           WHEN age = 35 THEN '三十五'
           WHEN age = 40 THEN '四十'
           ELSE '不是三十、三十五、四十'
           END AS age1
FROM staff
WHERE age IN (30, 35, 40, 45)
LIMIT 10;

我们看到可以通过 CASE 字段，然后 WHEN 后面写一个值，来判断字段的值和 WHEN 后面的值是否相等，但这也仅能判断相等的情况。如果是更复杂的情况，那么就要使用搜索 CASE 表达式，也就是 CASE 后面什么也不用加，直接把条件写在 WHEN 后面，这样不仅能判断相等的情况，还可以进行更复杂的判断。

SELECT age,
       CASE
           WHEN age < 30 THEN '青年'
           WHEN age < 45 THEN '中年'
           WHEN age < 60 THEN '老年'
           ELSE '耄耋'
           END AS age1
FROM staff
LIMIT 20;
/*
25    青年
32    中年
44    中年
39    中年
33    中年
30    中年
34    中年
27    青年
47    老年
39    中年
25    青年
61    耄耋
27    青年
33    中年
43    中年
43    中年
42    中年
28    青年
26    青年
34    中年
 */

CASE 表达式除了可以用于查询语句的 SELECT 列表，也可以出现在其他子句中，例如 WHERE、ORDER BY 等。这里我们使用一张新的表：people。

SELECT age,
       level
FROM people
WHERE level NOT IN ('工程师', '经济师', '会计师');
/*
30	助理工程师
27	助理馆员
24	经济员
28	助理经济师
49	高级政工师
38	助理经济师
33	助理经济师
33	助理经济师
 */

-- 我们注意到，如果没有 IS NULL 或者 IS NOT NULL 之类的判断的话
-- 那么默认 WHERE 后面是不包含null的，也就是在判断的时候直接就不考虑 NULL 值了
SELECT age,
       level
FROM people
WHERE CASE
          WHEN level NOT IN ('工程师', '经济师', '会计师') THEN true
          WHEN level IS NULL then true
          ELSE false -- 也可以不加, 因为默认是 NULL, NULL 为假
          END;
/*
24  <null>
30	助理工程师
27	助理馆员
24	经济员
28	助理经济师
21  <null>
49	高级政工师
38	助理经济师
33	助理经济师
31  <null>
41	<null>
33	助理经济师
 */
-- 我们注意到这样就筛选出来了，如果满足条件我们就标记为true
-- 一旦 WHERE 后面的是 true，那么这一行就会保留，所以当 level 为空的时候，我们也标记为 true 即可

ORDER BY 也是类似的，可以自己尝试。

CASE 表达式是标准的 SQL 功能，所有数据库都支持并且实现一致。除此之外，Oracle 还提供了一个专有函数：DECODE。

DECODE(expression, value1, result1, value2, result2, ...[, default_result ])

该函数依次比较表达式 expression 与 valueN 的值，如果找到相等的值就返回对应的 resultN；如果没有匹配到任何相等的值，返回默认结果 default_result；如果此时没有提供 default_result，返回 NULL 值。DECODE 是 Oracle 专有函数，推荐大家使用标准的 CASE 表达式。

MySQL 中的 DECODE 函数是一个解密函数，与此无关。

小结

CASE 表达式为 SQL 语句提供了逻辑处理的能力，可以基于不同的条件返回不同的结果。CASE 表达式支持两种形式：简单 CASE 表达式和搜索 CASE 表达式。