case when then else_CASE 表达式

一、语法及作用

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

CASE 

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 

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

SELECT 

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

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

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

SELECT 

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

SELECT 

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

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

SELECT 

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

SELECT 

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

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

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

方法一：UNION ALL

SELECT 

方法二：CASE WHEN 表达式

SELECT 

3.5 多列数据的最大值

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

方法一：自己想的

SELECT 

方法二：穷尽讨论

SELECT 

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

SELECT 

方法四：GREATEST() 函数

SELECT 

GREATEST() 函数介绍：

https://www.w3schools.com/sql/func_mysql_greatest.asp​www.w3schools.com

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

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

SELECT 

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

方法1：

SELECT 

方法2：

SELECT 

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

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

UPDATE 

3.8 调换主键值：

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

-- 1. 将 a 转换为中间值 d 

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

UPDATE 

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

四、练习

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

shanshant：SQL面试50题​zhuanlan.zhihu.com

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

方法一：自己写的

SELECT 

方法二：参考猴子老师的

SELECT 

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

SELECT