【SQL 中级语法 1】CASE 表达式

目录

1 CASE表达式概述

注意事项1：统一各分支返回的数据类型

注意事项2：不要忘了写END

注意事项3：养成写ELSE子句的习惯

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

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

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

5 在UPDATE语句里进行条件分支

6 表之间的数据匹配

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

小结

---

CASE表达式也许是SQL-92标准里加入的最有用的特性。如果能用好它，那么SQL能解决的问题就会更广泛，写法也会更加漂亮。而且，因为CASE表达式是不依赖于具体数据库的技术，所以可以提高SQL代码的可移植性。

1 CASE表达式概述

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

注意事项1：统一各分支返回的数据类型

一定要注意CASE表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他分支返回数值型的写法是不正确的。

注意事项2：不要忘了写END

注意事项3：养成写ELSE子句的习惯

不写ELSE子句时，CASE表达式的执行结果是NULL。但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上ELSE子句（即便是在结果可以为NULL的情况下）。养成这样的习惯后，我们从代码上就可以清楚地看到这种条件下会生成NULL，而且将来代码有修改时也能减少失误。

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

select case pref_name
    when '德岛' then '四国'
    when '香川' then '四国'
    when '爱媛' then '四国'
    when '高知' then '四国'
    when '福冈' then '九州'
    when '佐贺' then '九州'
    when '长崎' then '九州'
    else '其他' end as distinct,
    sum(population)
from PopTbl
group by distinct;   # group by 子句中引用了select子句中定义的别名

这里的GROUP BY子句使用的正是SELECT子句里定义的列的别称——district。但是严格来说，这种写法是违反标准SQL的规则的。因为GROUP BY子句比SELECT语句先执行，所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的。事实上，在Oracle、DB2、SQL Server等数据库里采用这种写法时就会出错。

不过也有支持这种SQL语句的数据库，例如在PostgreSQL和MySQL中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语句时，会先对SELECT子句里的列表进行扫描，并对列进行计算。不过因为这是违反标准的写法，所以这里不强烈推荐大家使用。但是，这样写出来的SQL语句确实非常简洁，而且可读性也很好。

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

这个技巧可贵的地方在于，它能将SQL的查询结果转换为二维表的格式。如果只是简单地用GROUP BY进行聚合，那么查询后必须通过宿主语言或者Excel等应用程序将结果的格式转换一下，才能使之成为交叉表。看上面的执行结果会发现，此时输出的已经是侧栏为县名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。

如果用一句话来形容这个技巧，可以这样说：新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支。如此好的技巧，请大家多使用。

（我会！）

4 用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）的逻辑表达式，记作P→Q。

这里需要重点理解的是蕴含式和逻辑与（logical product）的区别。逻辑与也是一个逻辑表达式，意思是“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的缩写（关于三值逻辑，后面会详细介绍）。

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

5 在UPDATE语句里进行条件分支

1. 例子

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

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万日元。

问题在于，第一次的UPDATE操作执行后，“当前工资”发生了变化，如果还拿它当作第二次UPDATE的判定条件，结果就会不准确。

正确结果可以使用case表达式来实现

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

case表达式的这种写法应用很广，可以在调换上表的主键值a和b时用CASE表达式。

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

6 表之间的数据匹配

与DECODE函数等相比，CASE表达式的一大优势在于能够判断表达式。也就是说，在CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词。因此，CASE表达式具有非常强大的表达能力。

课程表 CourseMaster

开设周期 OpenCourses

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

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

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

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

StudentClub

我们通过将其“主社团标志”列设置为Y或者N来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为N。

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

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

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

很容易想到的办法是，针对两个条件分别写SQL语句来查询。要想知道学生“是否加入了多个社团”，我们需要用HAVING子句对聚合结果进行判断。

    --条件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：选择加入了多个社团的学生
    SELECT std_id, club_id AS main_club
      FROM StudentClub
     WHERE main_club_flg ='Y';
 
 
-- 执行结果
 
    std_id  main_club
    ------  ----------
    100     1
    200     3

 使用CASE表达式

    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
    ------   ----------
    100       1
    200       3
    300       4
    400       5
    500       6

这种嵌套的写法让人有点眼花缭乱，其主要目的是用CASE WHEN COUNT(＊) = 1 ……ELSE ……．这样的CASE表达式来表示“只加入了一个社团还是加入了多个社团”这样的条件分支。

对聚合结果进行条件判断时要用HAVING子句，但这里可以看到，在SELECT语句里使用CASE表达式也可以完成同样的工作，这种写法比较新颖。如果用一句话来形容这个技巧，可以这样说：

新手用HAVING子句进行条件分支，高手用SELECT子句进行条件分支。

通过这道例题我们可以明白：CASE表达式用在SELECT子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部。这种高度自由的写法正是CASE表达式的魅力所在。

——————————————————————————————————————————

小结

CASE表达式经常会因为同VB和C语言里的CASE“语句”混淆而被叫作CASE语句。但是准确来说，它并不是语句，而是和1+1或者a/b一样属于表达式的范畴。结束符END确实看起来像是在标记一连串处理过程的终结，所以初次接触CASE表达式的人容易对这一点感到困惑。“表达式”和“语句”的名称区别恰恰反映了两者在功能处理方面的差异。

CASE表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正因为它是表达式，所以还可以写在SELECE子句、GROUP BY子句、WHERE子句、ORDER BY子句里。简单点说，在能写列名和常量的地方，通常都可以写CASE表达式。从这个意义上来说，与CASE表达式最接近的不是面向过程语言里的CASE语句，而是Lisp和Scheme等函数式语言里的case和cond这样的条件表达式。关于SQL和函数式语言的对比，会在之后提到。

1．在GROUP BY子句里使用CASE表达式，可以灵活地选择作为聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。

2．在聚合函数中使用CASE表达式，可以轻松地将行结构的数据转换成列结构的数据。

3．相反，聚合函数也可以嵌套进CASE表达式里使用。

4．相比依赖于具体数据库的函数，CASE表达式有更强大的表达能力和更好的可移植性。

5．正因为CASE表达式是一种表达式而不是语句，才有了这诸多优点。

——————————————————————————————————————————

更多文章请点击主页查看。