一、语法及作用
使用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子句里的CASE表达式复制到 GROUP BY 子句里。后期修改时有可能只改了一处而忘了另一处。因此,可以在 GROUP BY 子句中引用 SELECT 子句中定义的别名 (如下代码所示),这样写出来的 SQL 语句确实非常简洁,而且可读性也很好 。
严格来说,这种写法是违反标准 SQL 的规则的,因为 GROUP BY 子句比 SELECT 语句先执行。事实上,在 Oracle、DB2、 SQL Server 等数据库里采用这种写法时就会出错。 不过在 PostgreSQL 和 MySQL 中,这个查询语句就可以顺利执行。这是因为,这些数据库在执行查询语句时,会先对 SELECT 子句里的列表进行扫描,并对列进行计算。不过因为这是违反标准的写法,所以这里不强烈推荐大家使用。
3.2 行列转换:在聚合函数中使用CASE WHEN 表达式
3.2.1 统计下表 PopTbl2 中的内容,得出如右表“统计结果”所示的结果。
3.2.2 统计 PopTbl2 表中的容,得出如下表所示的结果。
3.3 表之间的数据匹配:在 CASE WHEN 表达式中使用子查询
方法一:子查询 (X 和 O 表示)
方法二:联结 (0 和 1表示)
3.4 在 CASE 表达式中使用聚合函数
如表 StudentClub 所示,这张表的主键是“学号、社团 ID”,存储了学生和社团之间多对多的关系。有的学生同时加入了多个社团(如学号为 100、200 的学生),有的学生只加入了某一个社团(如学号为 300、400、500 的学生)。对于加入了多个社团的学生,我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团;对于只加入了一个社团的学生,我们将其“主社团标志”列设置为 N。
接下来,我们需要查询获取只加入了一个社团的学生的社团 ID 以及获取加入了多个社团的学生的主社团 ID,并将结果存放在一个表中。
方法一:UNION ALL
方法二:CASE WHEN 表达式
3.5 多列数据的最大值
针对每一个key_col,取出x、y、z中最大的值 (取出每一行的最大值)
方法一:自己想的
方法二:穷尽讨论
方法三:运用 UNION ALL 进行行列转换
方法四:GREATEST() 函数
GREATEST() 函数介绍:
https://www.w3schools.com/sql/func_mysql_greatest.aspwww.w3schools.com
3.6 在 ORDER BY 语句中使用 CASE WHEN 表达式
3.6.1 针对3.5中的结果,将结果按照 B-A-D-C的顺序排序
3.6.2 如果job是"SALESMAN",则根据"comm"排序,否则根据"sal"排序
方法1:
方法2:
3.7 使用 CASE WHEN 语句在 UPDATE 语句里进行条件分支:
例如:对公司工资表中(Salaries)的员工工资(salary)进行更新:对当前工资为 30 万日元以上的员工,降薪 10%;对当前工资为 25 万日元以上且不满 28 万日元的员工,加薪 20%。
3.8 调换主键值:
通常,当我们想调换主键值 a 和 b 时,需要将主键值临时转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操作:
但是,如果使用 CASE 表达式,1 次就可以做到:
一般来说需要进行这样的调换大多是因为表的设计出现了问题,所以请先重新审视一下表的设计,去掉不必要的约束。
四、练习
表关系请参考如下文章 (SQL面试50题) 中的四张表
shanshant:SQL面试50题zhuanlan.zhihu.com
练习一:查询每门课程的及格人数和不及格人数
方法一:自己写的
方法二:参考猴子老师的
练习二:使用分段 [100-85], [85-70], [70-60], [<60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称。