SQL进阶教程 - 1-1 CASE表达式

• 在SQL里表达条件分支
  case表达式是SQL里非常重要而且使用起来非常便利的技术，我们应该学会用它来描述条件分支

case表达式概述
case表达式有简单case表达式（simple case expression）和搜索case表达式(searched case expression)两种写法

--简单case表达式
CASE sex
WHEN '1' THEN'男'
WHEN '2' THEN'女'
ELSE'其他' END;
--搜索case表达式
CASE WHEN sex = '1'THEN'男'
     WHEN sex = '2'THEN'女'
ELSE'其他' END;

简单case表达式能写的条件，搜索case表达式也能写

注意事项

1. 统一各分支返回的数据类型
2. 不要忘了写end
3. 养成写else子句的习惯（不写else子句时，case表达式的执行结果是null）

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

例如：统计下表PopTbl中的内容，得出“统计结果”所示的结果

--把县编号转换成地区编号
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子句里

--按人口数量等级划分都道府县
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;

--把县编号转换成地区编号，将case表达式归纳到一处
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 district;
--虽然简洁，但不建议使用，mysql和PostgreSQL支持
--因为GROUP BY子句比SELECT语句先执行，所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的

用一条SQL语句进行不同条件的统计
进行不同条件的统计是case表达式的著名用法之一
例如：统计下表PopTbl2中的数据，得出“统计结果”所示的结果

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 的查询结果转换为二维表的格式。

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

这两个约束的程序行为不一样请参考下面这个关于逻辑与和蕴含式的真值表。
U 是 SQL 中三值逻 辑的特有值 unknown 的缩写
在update语句里进行条件分支

--用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语句最后一行的else salary非常重要
如果没有它，不满足条件1和条件2的员工工资就会被更新为null*/

表之间的数据匹配
我们需要做的是，检查表 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 里数据比较多的时候 更有优势。

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

如表 StudentClub 所示，这张表的主键 是“学号、社团 ID”，存储了学生和社团之间多对多的关系。

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 子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部。
这种高度自由的写法正是 CASE 表达式的魅力所在

本节要点

1. 在 GROUP BY 子句里使用 CASE 表达式，可以灵活地选择作为聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。
2. 在聚合函数中使用 CASE 表达式，可以轻松地将行结构的数据转换成列结构的数据。
3. 相反，聚合函数也可以嵌套进 CASE 表达式里使用。
4. 相比依赖于具体数据库的函数，CASE 表达式有更强大的表达能力和更好的可移植性。
5. 正因为 CASE 表达式是一种表达式而不是语句，才有了这诸多优点。