SQL进阶技巧——CASE表达式全解！

关于SQL我之前已经陆续更新了很多期，从入门到基础知识再到练习和面试技巧都有分享，大家感兴趣的话可以去看下这篇文章。

戎易大数据：几万字汇总了SQL全部知识点，必看！（含教程、面试技巧）

近几期主要围绕SQL进阶技巧来进行分享。

本篇主题为CASE表达式。

CASE表达式是SQL里非常重要而且使用起来非常便利的技术，它通常被用来描述条件分支。本篇文章我们将通过 行列转换 、已有 数据重分组 （分类）、 与约束的结合使用 等例子，来介绍CASE表达式的用法。

CASE表达式的优点在于它不依赖于具体数据库的技术，不像Oracle的DECODE函数、MySQL的IF函数等，局限于具体的数据库，所以利用好CASE表达式可以提高我们的SQL代码的可移植性。

01、CASE表达式基本形式

CASE表达式分两种写法，分别是 简单 CASE表达式和 搜索 CASE表达式，具体语法如下：

--简单CASE表达式
CASE a
WHEN '1' THEN 'b'
WHEN '2' THEN 'c'
ELSE '其他' END
--搜索CASE表达式
CASE WHEN a ='1' THEN 'b'
WHEN a ='2' THEN 'c'
ELSE '其他' END

上面两种写法的执行结果相同，但是正如其名，简单CASE表达式写法简单，能实现的需求比较 有限 ，并且简单CASE表达式能实现的，搜索CASE表达式都能实现，所以通常建议采用搜索CASE表达式的写法。

当我们在写SQL语句时需要注意，当发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，而剩余的WHEN子句会被忽略。我们需要注意WHEN子句的条件的 排他性 来避免引起语句混乱。

WHEN子句被忽略的写法：

--按照下方的写法，结果里不会出现“第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

另外在使用CASE表达式的时候，还需要注意以下几点：

• 注意事项1 ：注意CASE表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他分支返回数值型的写法是不正确的。
• 注意事项2 ：使用CASE表达式的时候不要忘了写END，通常感觉自己写得没问题，而执行时却出错的情况大多是由这个原因引起的，所以一定注意一下。
• 注意事项3 ：养成写ELSE子句的习惯。与END不同，ELSE子句是可选的，不写也不会出错。不写ELSE子句时，CASE表达式的执行结果是NULL。但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上ELSE子句（即便是在结果可以为NULL的情况下）。养成这样的习惯后，我们从代码上就可以清楚地看到这种条件下会生成NULL，而且将来代码有修改时也能减少失误。

02、将已有编号方式转换为新的方式并统计

在进行统计时，我们经常会遇到将已有编号方式转换为另外一种便于分析的方式并进行统计的需求。

例如，现在有一张按照“‘1：北海道’、‘2：青森’、……、‘47：冲绳’”这种编号方式来统计 都道府县 人口的表，我们需要以东北、关东、九州等 地区 为单位来分组，并统计人口数量。具体来说，就是统计下表PopTbl中的内容，得出如右表“统计结果”所示的结果。

数据源表PopTbl：

统计结果：

在不利用CASE表达式的情况下，我们可以通过定义一个包含“地区编号”列的 视图 来实现，但是一旦这样操作，需要添加的列的数量将等同于统计对象的编号个数，而且很难动态地修改。

而如果使用CASE表达式，则用如下所示的一条SQL语句就可以完成。为了便于理解，这里用县名（pref_name）代替编号作为GROUP BY的列。

--把县编号转换成地区编号(1)
    SELECT  CASE pref_name
                    WHEN '德岛' THEN '四国'
                    WHEN '香川' THEN '四国'
                    WHEN '爱媛' THEN '四国'
                    WHEN '高知' THEN '四国'
                    WHEN '福冈' THEN '九州'
                    WHEN '佐贺' THEN '九州'
                    WHEN '长崎' THEN '九州'
            ELSE '其他' END AS district,
            SUM(population)
      FROM  PopTbl
      GROUP BY CASE pref_name
                    WHEN '德岛' THEN '四国'
                    WHEN '香川' THEN '四国'
                    WHEN '爱媛' THEN '四国'
                    WHEN '高知' THEN '四国'
                    WHEN '福冈' THEN '九州'
                    WHEN '佐贺' THEN '九州'
                    WHEN '长崎' THEN '九州'
              ELSE '其他' END;

这里的关键在于将SELECT子句里的CASE表达式复制到GROUP BY子句里。需要注意的是，如果对转换前的列“pref_name”进行GROUP BY，就得不到正确的结果（因为这并不会引起语法错误，所以容易被忽视）。

同样地，也可以将数值按照适当的级别进行分类统计。例如，要按人口数量等级（pop_class）查询都道府县个数的时候，就可以像下面这样写SQL语句。

--按人口数量等级划分都道府县
SELECT  
    CASE WHEN population <  100 THEN'01'
    WHEN population >= 100 AND population < 200  THEN '02'
    WHEN population >= 200 AND population < 300  THEN '03'
    WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(＊) AS cnt
FROM  PopTbl
GROUP BY 
    CASE WHEN population < 100 THEN'01'
    WHEN population >= 100 AND population < 200  THEN'02'
    WHEN population >= 200 AND population < 300  THEN'03'
    WHEN population >= 300 THEN'04'
    ELSE NULL END;

这个技巧非常好用，不过，必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式。这有点儿麻烦，后期需要修改的时候，很容易发生只改了这一处而忘掉改另一处的失误。

我们可以在GROUP BY子句处引用SELECT子句中的别名来避免这种情况，但是因为GROUP BY子句比SELECT语句先执行，所以这样做并不符合标准的SQL规则。

03、用一条SQL语句进行不同条件的统计

进行不同条件的统计是CASE表达式的著名用法之一。例如，我们需要往存储各县人口数量的表PopTbl里添加上“性别”列，然后求按性别、县名汇总的人数。具体来说，就是统计表PopTbl2中的数据，然后求出如表“统计结果”所示的结果。

统计源表PopTbl2：

结果：

通常的做法是通过在WHERE子句里分别写上不同的条件，然后执行两条SQL语句来查询。

-- 男性人口
SELECT pref_name,
       SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
       SUM(population)
FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name;

最后需要通过宿主语言或者应用程序将查询结果按列展开。如果使用UNION，只用一条SQL语句就可以实现，但使用这种做法时，工作量并没有减少，SQL语句也会变得很长。而如果使用CASE表达式，下面这一条简单的SQL语句就可以搞定。

SELECT pref_name,
--男性人口
       SUM( CASE WHEN sex ='1' THEN population ELSE 0 END) AS cnt_m,
--女性人口
       SUM( CASE WHEN sex ='2' THEN population ELSE 0 END) AS cnt_f
FROM  PopTbl2
GROUP BY pref_name;

上面这段代码所做的是，分别统计每个县的“男性”（即’1'）人数和“女性”（即’2'）人数。也就是说，这里是将“ 行结构 ”的数据转换成了“ 列结构 ”的数据。除了SUM, COUNT、AVG等聚合函数也都可以用于将行结构的数据转换成列结构的数据。

这个技巧可贵的地方在于，它能将SQL的查询结果转换为二维表的格式。如果只是简单地用GROUP BY进行聚合，那么查询后必须通过宿主语言或者Excel等应用程序将结果的格式转换一下，才能使之成为 交叉表 。

看上面的执行结果会发现，此时输出的已经是侧栏为县名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。

04、用CHECK约束定义多个列的条件关系

其实，CASE表达式和 CHECK约束 是很般配的一对组合。也许有很多数据库工程师不怎么用CHECK约束，但是一旦他们了解了CHECK约束和CASE表达式结合使用之后的强大威力，就一定会跃跃欲试的。

假设某公司规定“女性员工的工资必须在20万日元以下”，而在这个公司的人事表中，这条无理的规定是使用CHECK约束来描述的，代码如下所示。

CONSTRAINT check_salary CHECK
 ( CASE WHEN sex ='2' 
     THEN CASE WHEN salary <= 200000
       THEN 1 ELSE 0 END
     ELSE 1 END = 1 )

在这段代码里，CASE表达式被嵌入到CHECK约束里，描述了“如果是女性员工，则工资是20万日元以下”这个命题。在命题逻辑中，该命题是叫作 蕴含式 的逻辑表达式，记作P→Q。

这里需要重点理解的是蕴含式和逻辑“与”的区别。逻辑与也是一个逻辑表达式，意思是“P且Q”，记作P∧Q。用逻辑“与”改写的CHECK约束如下所示。

CONSTRAINT check_salary CHECK
   ( sex ='2' AND salary <= 200000)

当然，这两个约束的程序行为不一样。究竟哪里不一样呢？请大家先思考一下，再看下面的答案和解释。

答案 ：如果在CHECK约束里使用逻辑“与”，该公司将不能雇佣男性员工。而如果使用蕴含式，男性也可以在这里工作。

解释 ：要想让逻辑“与”P∧Q为真，需要命题P和命题Q 均为真 ，或者一个为真且另一个无法判定真假。也就是说，能在这家公司工作的是“性别为女且工资在20万日元以下”的员工，以及性别或者工资无法确定的员工（如果一个条件为假，那么即使另一个条件无法确定真假，也不能在这里工作）。而要想让蕴含式P→Q为真，需要命题P和命题Q均为真，或者P为假，或者P无法判定真假。也就是说如果不满足“是女性”这个前提条件，则无需考虑工资约束。

请参考下面这个关于逻辑“与”和蕴含式的真值表。U是SQL中三值逻辑的特有值unknown的缩写。

根据上表所示，蕴含式在员工性别不是女性（或者无法确定性别）的时候为真，可以说相比逻辑“与”约束更加宽松。

05、在UPDATE语句里进行条件分支

下面请大家思考一下这样一种需求：以某数值型的列的当前值为判断对象，将其更新成别的值。这里的问题是，此时UPDATE操作的条件会有多个分支。例如，我们通过下面这样一张公司人事部的员工工资信息表Salaries来看一下这种情况。

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

1．对当前工资为30万日元以上的员工，降薪10%。

2．对当前工资为25万日元以上且不满28万日元的员工，加薪20%。

按照这些要求更新完的数据应该如下表所示。

我们的第一反应通常是分别执行下面两个UPDATE操作好像就可以做到，但这样的结果却是不正确的。

--条件1
UPDATE Salaries
SET salary = salary ＊ 0.9
WHERE salary >= 300000;
--条件2
UPDATE Salaries
SET salary = salary ＊ 1.2
WHERE salary >= 250000 AND salary < 280000;

接下来分析一下不正确的原因。

例如这里有一个员工，当前工资是30万日元，按“条件1”执行UPDATE操作后，工资会被更新为27万日元，但继续按“条件2”执行UPDATE操作后，工资又会被更新为32.4万日元。这样，本来应该被降薪的员工却被加薪了2.4万日元。

这样的结果当然并非人事部所愿。员工相田的工资必须被准确地降为27万日元。问题在于，第一次的UPDATE操作执行后，“ 当前工资 ”发生了 变化 ，如果还拿它当作第二次UPDATE的判定条件，结果就会不准确。

然而，即使将两条SQL语句的执行顺序颠倒一下，当前工资为27万日元的员工，其工资的更新结果也会出现问题。为了避免这些问题，准确地表达出人事部长的意图，可以像下面这样用CASE表达式来写SQL。

--用CASE表达式写正确的更新操作
UPDATE Salaries
SET salary = 
    CASE WHEN salary >= 300000 THEN salary ＊ 0.9
    WHEN salary >= 250000 AND salary < 280000 THEN salary ＊ 1.2
    ELSE salary END;

这条SQL语句不仅执行结果正确，而且因为 只需执行一次 ，所以速度也更快。

需要注意的是，SQL语句最后一行的 ELSE salary 非常重要，必须写上。因为如果没有它，条件1和条件2都不满足的员工的工资就会被更新成NULL。

这一点与CASE表达式的设计有关，在刚开始介绍CASE表达式的时候我们就已经了解到，如果CASE表达式里没有明确指定ELSE子句，执行结果会被默认地处理成ELSE NULL。现在大家明白笔者最开始强调使用CASE表达式时要习惯性地写上ELSE子句的理由了吧？

这个技巧的应用范围很广。例如，可以用它简单地完成 主键值调换 这种繁重的工作。通常，当我们想调换主键值a和b时，需要将主键值临时转换成某个中间值。使用这种方法时需要执行3次UPDATE操作，但是如果使用CASE表达式，1次就可以做到。

如果在调换上表的主键值a和b时不用CASE表达式，则需要像下面这样写3条SQL语句。

--1．将a转换为中间值d
UPDATE SomeTable
SET p_key ='d'
WHERE p_key ='a';
--2．将b调换为a
UPDATE SomeTable
SET p_key ='a'
WHERE p_key ='b';
--3．将d调换为b
UPDATE SomeTable
SET p_key ='b'
WHERE p_key ='d';

像上面这样做，结果确实没有问题。只是，这里没有必要执行3次UPDATE操作，而且中间值d是否总能使用也是问题。而如果使用CASE表达式，就不必担心这些，1次就可以完成调换。

--用CASE表达式调换主键值
UPDATE SomeTable
SET p_key = 
    CASE WHEN p_key ='a' THEN 'b'
    WHEN p_key ='b' THEN 'a'
    ELSE p_key END
WHERE p_key IN ('a', 'b');

显而易见，这条SQL语句按照“如果是a则更新为b，如果是b则更新为a”这样的条件分支进行了UPDATE操作。不只是主键，唯一键的调换也可以用同样的方法进行。

本例的关键点和上一例的加薪与降薪一样，即用CASE表达式的条件分支进行的更新操作是一气呵成的，因此可以避免出现主键重复所导致的错误。

但是，一般来说需要进行这样的调换大多是因为表的设计出现了问题，所以请先重新审视一下表的设计，去掉不必要的约束。

06、表之间的数据匹配

与DECODE函数等相比，CASE表达式的一大优点在于能够判断表达式。因为在CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等 谓词组合 ，以及能 嵌套子查询 的IN和EXISTS谓词。

如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月所设课程的表。

课程一览：

开设的课程：

我们接下来要用这两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程。

course_name   6月   7月   8月
-----------  ----  ---- ----
会计入门        ○    ×    ×
财务知识        ×    ×    ○
簿记考试        ○    ×    ×
税务师          ○    ○    ○

我们现在需要做的是，检查表OpenCourses中的各月里有表CourseMaster中的哪些课程。这个匹配条件可以用CASE表达式来实现。

--表的匹配：使用IN谓词
    SELECT course_name,
          CASE WHEN course_id IN
                        (SELECT course_id FROM OpenCourses
                          WHERE month = 200706) THEN'○'
                ELSE'×'END AS "6月",
          CASE WHEN course_id IN
                        (SELECT course_id FROM OpenCourses
                          WHERE month = 200707) THEN'○'
                ELSE'×'END AS "7月",
          CASE WHEN course_id IN
                        (SELECT course_id FROM OpenCourses
                          WHERE month = 200708) THEN'○'
                ELSE'×'END  AS "8月"
      FROM CourseMaster;
    --表的匹配：使用EXISTS谓词
    SELECT CM.course_name,
          CASE WHEN EXISTS
                        (SELECT course_id FROM OpenCourses OC
                          WHERE month = 200706
                              AND OC.course_id = CM.course_id) THEN'○'
                  ELSE'×'END AS "6月",
              CASE WHEN EXISTS
                          (SELECT course_id FROM OpenCourses OC
                            WHERE month = 200707
                              AND OC.course_id = CM.course_id) THEN'○'
                  ELSE'×'END AS "7月",
              CASE WHEN EXISTS
                          (SELECT course_id FROM OpenCourses OC
                            WHERE month = 200708
                              AND OC.course_id = CM.course_id) THEN'○'
                  ELSE'×'END  AS "8月"
        FROM CourseMaster CM;

上述查询没有进行聚合，因此也不需要排序，月份增加时只需要修改SELECT子句，语句的扩展性比较好。

无论使用IN还是EXISTS，得到的结果是一样的，但从性能方面来说， EXISTS更好 。通过EXISTS进行的子查询能够用到“month, course_id”这样的主键索引，因此尤其是当表OpenCourses里数据比较多的时候EXISTS更有优势。

07、在CASE表达式中使用聚合函数

接下来给大家介绍一下稍微高级的用法。这个用法乍一看可能让人觉得像是语法错误，实际上却并非如此。

我们来看一个例子，假设这里有一张显示了学生及其加入的社团的一览表。如表StudentClub所示，这张表的主键是“学号、社团ID”，存储了学生和社团之间多对多的关系。

有的学生同时加入了多个社团（如学号为100、200的学生），有的学生只加入了某一个社团（如学号为300、400、500的学生）。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为Y或者N来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为N。

接下来，我们按照下面的条件查询这张表里的数据。

1．获取只加入了一个社团的学生的社团ID。

2．获取加入了多个社团的学生的主社团ID。

我们最直接的想法是，针对两个条件分别写两个SQL语句来查询。

要想知道学生“是否加入了多个社团”，我们还需要用 HAVING子句 对聚合结果进行判断。

条件1的SQL：

--条件1：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(＊) = 1;

结果：

std_id   main_club
------   ----------
300       4
400       5
500       6

条件2的SQL：

--条件2：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg ='Y';

结果：

std_id  main_club
------  ----------
100     1
200     3

这样做也能得到正确的结果，但需要写 多条SQL语句 。而如果使用CASE表达式，下面这 一条SQL语句 就可以了。

 SELECT  std_id,
            CASE WHEN COUNT(＊) = 1  --只加入了一个社团的学生
                THEN MAX(club_id)
                ELSE MAX(CASE WHEN main_club_flg ='Y'
                              THEN club_id
                              ELSE NULL END)
            END AS main_club
      FROM StudentClub
     GROUP BY std_id;

结果：

std_id   main_club