CASE 表达式

一、语法及作用

使用CASE表达式可以帮助我们解决复杂的查询问题，相当于条件判断的函数，判断每一行是不是满足条件。

CASE WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     ELSE(表达式)
END;

CASE 表达式会从对最初的WHEN子句中的“< 求值表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真(TRUE)，那么就返回THEN子句中的表达式，CASE表达式的执行到此为止。如果结果不为真，那么就跳转到下一条WHEN子句的求值之中。 如果直到最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的表达式，执行终止。

二、注意事项

• 在发现为真的 WHEN 子句时， CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。因此，为了避免引起不必要的混乱，使用 WHEN 子句时要注意条件的排他性。
• CASE 表达式里各个分支返回的数据类型必须一致，如果不一致，则需使用CAST()函数转换数据类型。
• 虽然CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易理解，还是尽量不要省略。
• CASE 表达式中的 END 不能省略。
• 使用CASE表达式能够将SELECT语句的结果进行组合。

三、适用情况

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

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

首先，可以通过定义一个包含“地区编号”列的视图来实现。但是这种方法需要添加的行的数量将等同于统计对象的编号个数，而且很难动态地修改。因此，可以使用CASE表达式来实现:

SELECT CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
            WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
            ELSE '其他'
            END AS district,
       SUM(population) AS population
FROM PopTbl
GROUP BY CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
              WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
              ELSE '其他'
ORDER BY population DESC;

这里的关键在于将SELECT子句里的CASE表达式复制到 GROUP BY 子句里。后期修改时有可能只改了一处而忘了另一处。因此， 可以在 GROUP BY 子句中引用 SELECT 子句中定义的别名 (如下代码所示)，这样写出来的 SQL 语句确实非常简洁，而且可读性也很好 。

SELECT CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
            WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
            ELSE '其他'
            END AS district,
       SUM(population) AS population
FROM PopTbl
GROUP BY district
ORDER BY population DESC;

严格来说，这种写法是违反标准 SQL 的规则的，因为 GROUP BY 子句比 SELECT 语句先执行。事实上，在 Oracle、DB2、 SQL Server 等数据库里采用这种写法时就会出错。 不过 在 PostgreSQL 和 MySQL 中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。 不过因为这是违反标准的写法，所以这里不强烈推荐大家使用。

3.2 行列转换：在聚合函数中使用CASE WHEN 表达式

3.2.1 统计下表 PopTbl2 中的内容，得出如右表“统计结果”所示的结果。

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

3.2.2 统计 PopTbl2 表中的容，得出如下表所示的结果。

SELECT CASE WHEN sex = 1 THEN 'male' ELSE 'female' END AS sex2,
SUM(population) AS 'total',
SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS '德岛',
SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS '香川',
SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS '爱媛',
SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS '高知',
SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN population ELSE 0 END) AS '四国'
FROM PopTbl2
GROUP BY sex2;

3.3 表之间的数据匹配：在 CASE WHEN 表达式中使用子查询

方法一：子查询 (X 和 O 表示)

SELECT course_name,
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200706')
 THEN 'O' ELSE 'X' END AS 'June',
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200707')
 THEN 'O' ELSE 'X' END AS 'July',
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200708')
 THEN 'O' ELSE 'X' END AS 'August'
FROM CourseMaster;

方法二：联结 (0 和 1表示)

SELECT a.course_name,
 MAX(CASE WHEN b.month = '200706' THEN 1 ELSE 0 END) AS 'June',
 MAX(CASE WHEN b.month = '200707' THEN 1 ELSE 0 END) AS 'July',
 MAX(CASE WHEN b.month = '200708' THEN 1 ELSE 0 END) AS 'August'
FROM CourseMaster AS a
LEFT JOIN OpenCourses AS b ON a.course_id = b.course_id
GROUP BY a.course_name;

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

如表 StudentClub 所示，这张表的主键是“学号、社团 ID”，存储了学生和社团之间多对多的关系。有的学生同时加入了多个社团(如学号为 100、200 的学生)，有的学生只加入了某一个社团(如学号为 300、400、500 的学生)。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N。

接下来，我们需要查询获取只加入了一个社团的学生的社团 ID 以及获取加入了多个社团的学生的主社团 ID，并将结果存放在一个表中。

方法一：UNION ALL

SELECT std_id, MAX(club_id) AS 'main_club' FROM StudentClub
GROUP BY std_id
HAVING COUNT(club_id) = 1
UNION ALL
SELECT std_id, club_id AS 'main_club' FROM StudentClub
WHERE main_club_flg = 'Y'
ORDER BY std_id;

方法二：CASE WHEN 表达式

SELECT std_id, 
CASE WHEN COUNT(club_id) = 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;

3.5 多列数据的最大值

针对每一个key_col，取出x、y、z中最大的值 (取出每一行的最大值)

方法一：自己想的

SELECT key_col,
 CASE WHEN x > y AND x > z THEN x
      WHEN y > x AND y > z THEN y
      ELSE z
 END AS 'max_col'
FROM Greatests;

方法二：穷尽讨论

SELECT key_col,
       CASE WHEN CASE WHEN x < y THEN x ELSE y END < z THEN z ELSE
       CASE WHEN x < y THEN y ELSE x END
       END AS greatest
FROM Greatests;

方法三：运用 UNION ALL 进行行列转换

SELECT key_col, MAX(col) AS greatest FROM
 (SELECT key_col, x AS col FROM Greatests
  UNION ALL
  SELECT key_col, y AS col FROM Greatests
  UNION ALL
  SELECT key_col, z AS col FROM Greatests) AS a
GROUP BY key_col;

方法四：GREATEST() 函数

SELECT key_col, GREATEST(x, y, z) AS greatest 
FROM Greatests;

GREATEST() 函数介绍：

3.6 在 ORDER BY 语句中使用 CASE WHEN 表达式

3.6.1 针对3.5中的结果，将结果按照 B-A-D-C的顺序排序

SELECT key_col,
       CASE WHEN CASE WHEN x < y THEN x ELSE y END < z THEN z ELSE
       CASE WHEN x < y THEN y ELSE x END
       END AS greatest
FROM Greatests
ORDER BY 
 (CASE WHEN key_col = 'B' THEN 1
       WHEN key_col = 'A' THEN 2
       WHEN key_col = 'D' THEN 3
       ELSE 4 END);

3.6.2 如果job是"SALESMAN",则根据"comm"排序，否则根据"sal"排序

方法1：

SELECT ename, sal, job, comm FROM emp
ORDER BY 
 CASE WHEN job = 'SALESMAN' THEN comm
 ELSE sal
 END;

方法2：

SELECT ename, sal, job, comm,
 CASE WHEN job = 'SALESMAN' THEN comm
 ELSE sal
 END AS ordered
FROM emp
ORDER BY 5;

3.7 使用 CASE WHEN 语句在 UPDATE 语句里进行条件分支：

例如：对公司工资表中(Salaries)的员工工资(salary)进行更新：对当前工资为 30 万日元以上的员工，降薪 10%；对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

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;

3.8 调换主键值：

通常，当我们想调换主键值 a 和 b 时，需要将主键值临时转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操作：

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

但是，如果使用 CASE 表达式，1 次就可以做到：

UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
                 WHEN p_key = 'b' THEN 'a'
		 ELSE p_key
                 END;

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

四、练习

表关系请参考如下文章 (SQL面试50题) 中的四张表

练习一：查询每门课程的及格人数和不及格人数

方法一：自己写的

SELECT c_id, SUM(及格) AS 及格人数, SUM(不及格) AS 不及格人数
(SELECT c_id,
 CASE WHEN score >=60 THEN 1 
 ELSE 0 
 END AS '及格',
 CASE WHEN score <60 THEN 1 
 ELSE 0 
 END AS '不及格'
 FROM scores) AS c
GROUP BY c_id;

方法二：参考猴子老师的

SELECT c_id,
SUM(CASE WHEN score >= 60 THEN 1
    ELSE 0
    END) AS 及格人数,
SUM(CASE WHEN score < 60 THEN 1
    ELSE 0
    END) AS 不及格人数
FROM scores
GROUP BY c_id;

练习二：使用分段 [100-85], [85-70], [70-60], [<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称。

SELECT a.c_id, b.c_name,
SUM(CASE WHEN score >= 85 THEN 1
    ELSE 0
    END) AS 85至100分数段,
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1
    ELSE 0
    END) AS 70至85分数段,
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1
    ELSE 0
    END) AS 60至70分数段,
SUM(CASE WHEN score < 60 THEN 1