阅读之前看这里👉:博主是正在学习数据分析的一员,博客记录的是在学习过程中一些总结,也希望和大家一起进步,在记录之时,未免存在很多疏漏和不全,如有问题,还请私聊博主指正。
博客地址:天阑之蓝的博客,学习过程中不免有困难和迷茫,希望大家都能在这学习的过程中肯定自己,超越自己,最终创造自己。
MYSQL常见面试题之——CASE专题知识总结
复习知识点:
CASE表达式:
-- 简单CASE 表达式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- 搜索CASE 表达式
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
用一条SQL语句进行不同条件的统计(分组汇总求和问题)
进行不同条件的统计是CASE 表达式的著名用法之一。例如,我们需要往存储各县人口数量的表PopTbl 里添加上“性别”列,然后求按性别、县名汇总的人数。具体来说,就是统计表PopTbl2 中的数据,然后求出如表“统计结果”所示的结果。
通常的做法是像下面这样,通过在WHERE 子句里分别写上不同的条件,
然后执行两条SQL 语句来查询。
-- 男性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name;
最后需要通过宿主语言或者应用程序将查询结果按列展开。如果使用UNION,只用一条SQL 语句就可以实现,但使用这种做法时,工作量并没有减少,SQL 语句也会变得很长。而如果使用CASE 表达式,下面这一条简单的SQL 语句就可以搞定。
SELECT pref_name AS 县名,
SUM(CASE sex WHEN 1 THEN population ELSE 0 END) AS 男,
SUM(CASE sex WHEN 2 THEN population ELSE 0 END) AS 女
FROM poptbl2
GROUP BY 县名
结果如下:
用CASE调换列表中的值
下面思考一下这样一种需求:以某数值型的列的当前值为判断对象,将其更新成别的值。这里的问题是,此时UPDATE 操作的条件会有多个分支。例如,我们通过下面这样一张公司人事部的员工工资信息表Salaries 来看一下这种情况。
假设现在需要根据以下条件对该表的数据进行更新。
- 对当前工资为30 万日元以上的员工,降薪10%。
- 对当前工资为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 万日元。
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;
这个技巧的应用范围很广。例如,可以用它简单地完成主键值调换这种繁重的工作。通常,当我们想调换主键值a 和b 时,需要将主键值临时转换成某个中间值。使用这种方法时需要执行3 次UPDATE 操作,但是如果使用CASE 表达式,1 次就可以做到。
如果在调换上表的主键值a 和b 时不用CASE 表达式,则需要像下面这样写3 条SQL 语句。
--1. 将a 转换为中间值d
UPDATE SomeTable
SET p_key = 'd'
WHERE p_key = 'a'