【SQL进阶教程】CASE表达式

CASE表达式概述

CASE表达式有简单CASE表达式(simple case expression)和搜索CASE表达式(searched case expression)两种写法。
在编写SQL语句的时候需要注意，在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，而剩余的WHEN子句会被忽略。为了避免引起不必要的混乱，使用WHEN子句时要注意条件的排他性。

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

统一各分支返回的数据类型
不要忘了写END
养成写ELSE子句的习惯

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

在进行非定制化统计时，经常会遇到将已有编号方式转换为另外一种便于分析的方式并进行统计的需求。
例如，现在有一张按照“’1:北海道’、’2:青森’、…、’47:冲绳’”这种编号方式来统计都道府县人口的表，需要以东北、关东、九州等地区为单位来分组，并统计人口数量。

如果使用CASE表达式，则用如下所示的一条SQL语句就可以完成。

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，就得不到正确的结果。

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

进行不同条件的统计是CASE表达式的著名用法之一。例如，需要往存储各县人口数量的表PopTbl里添加上“性别”列，然后求按性别、县名汇总的人数。

如果使用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;

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

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

其实，CASE表达式和CHECK约束是很般配的一对组合。
假设某公司规定“女性员工的工资必须在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万日元以下”这个命题。
这里需要重点理解的是蕴含式(conditional)和逻辑与(logical product)的区别。用逻辑与改写的CHECK约束如下所示。

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

当然，这两个约束的程序行为不一样。
如果在CHECK约束里使用逻辑与，该公司将不能雇佣男性员工。而如果使用蕴含式，男性也可以在这里工作。

在UPDATE语句里进行条件分支

以某数值型的列的当前值为判断对象，将其更新成别的值。例如，通过下面这样一张公司人事部的员工工资信息表Salaries来看一下这种情况。

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

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

为了避免问题，准确地表达出可恶的人事部长的意图，可以像下面这样用CASE表达式来写SQL。

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语句不仅执行结果正确，而且因为只需执行一次，所以速度也更快。

表之间的数据匹配

在CASE表达式里，可以使用BETWEEN、LIKE、<、>等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词。因此，CASE表达式具有非常强大的表达能力。
如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月所设课程的表。

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

-- IN
SELECT course_name,
       CASE WHEN course_id IN 
                (SELECT course_id 
                 FROM OpenCourses
                 WHERE month = 200706) THEN 'O'
            ELSE 'X'
        END AS '6月',
        CASE WHEN course_id IN 
                (SELECT course_id 
                 FROM OpenCourses
                 WHERE month = 200707) THEN 'O'
             ELSE 'X'
        END AS '7月',
        CASE WHEN course_id IN 
                (SELECT course_id 
                 FROM OpenCourses
                 WHERE month = 200708) THEN 'O'
             ELSE 'X'
        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 'O'
            ELSE 'X'
        END AS '6月',
        CASE WHEN EXISTS
                (SELECT course_id 
                 FROM OpenCourses OC
                 WHERE month = 200707
                        AND OC.course_id = CM.course_id) THEN 'O'
             ELSE 'X'
        END AS '7月',
        CASE WHEN EXISTS
                (SELECT course_id 
                 FROM OpenCourses OC
                 WHERE month = 200708
                        AND OC.course_id = CM.course_id) THEN 'O'
             ELSE 'X'
        END AS '8月'
FROM CourseMaster CM;

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

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

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

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

获取只加入了一个社团的学生的社团ID。
获取加入了多个社团的学生的主社团ID。

如果使用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;

CASE表达式用在SELECT子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部。