阅读之前看这里👉：博主是正在学习数据分析的一员，博客记录的是在学习过程中一些总结，也希望和大家一起进步，在记录之时，未免存在很多疏漏和不全，如有问题，还请私聊博主指正。 博客地址：天阑之蓝的博客，学习过程中不免有困难和迷茫，希望大家都能在这学习的过程中肯定自己，超越自己，最终创造自己。

MYSQL常见面试题之——CASE专题知识总结

CASE表达式：用一条SQL语句进行不同条件的统计(分组汇总求和问题)用CASE调换列表中的值表之间的数据匹配在CASE 表达式中使用聚合函数 面试题练习滴滴2020年面试题：如何找出最小的N个数？面试题2：如何分析红包领取情况

复习知识点：

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

像上面这样做，结果确实没有问题。只是，这里没有必要执行3 次UPDATE 操作，而且中间值d 是否总能使用也是问题。而如果使用CASE 表达式，就不必担心这些，1 次就可以完成调换。

-- 用CASE 表达式调换主键值 UPDATE SomeTable SET p_key = CASE WHEN p_key = 'a' THEN 'b' WHEN p_key = 'b' THEN 'a' ELSE p_key END WHERE p_key IN ('a', 'b');

显而易见，这条SQL 语句按照“如果是a 则更新为b，如果是b 则更新为a”这样的条件分支进行了UPDATE 操作。不只是主键，唯一键的调换也可以用同样的方法进行。本例的关键点和上一例的加薪与降薪一样，即用CASE 表达式的条件分支进行的更新操作是一气呵成的，因此可以避免出现主键重复所导致的错误。

表之间的数据匹配

与DECODE 函数等相比，CASE 表达式的一大优势在于能够判断表达式。也就是说，在CASE 表达式里，我们可以使用BETWEEN、LIKE 和<、 > 等便利的谓词组合，以及能嵌套子查询的IN 和EXISTS 谓词。因此，CASE表达式具有非常强大的表达能力。

如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月所设课程的表。 我们要用这两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程。 我们需要做的是，检查表OpenCourses 中的各月里有表CourseMaster中的哪些课程。这个匹配条件可以用CASE 表达式来写。

SELECT course_name, CASE WHEN course_id in (SELECT course_id FROM Opencourses WHERE month = 200706) THEN 'O' ELSE 'X' END as '六月', CASE WHEN course_id in (SELECT course_id FROM Opencourses WHERE month = 200707) THEN 'O' ELSE 'X' END as '七月', CASE WHEN course_id in (SELECT course_id FROM Opencourses WHERE month = 200708) THEN 'O' ELSE 'X' END as '八月' FROM CourseMaster

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

新手用HAVING子句进行条件分支，高手用SELECT子句进行条件分支

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

接下来，我们按照下面的条件查询这张表里的数据。

获取只加入了一个社团的学生的社团ID。获取加入了多个社团的学生的主社团ID。

很容易想到的办法是，针对两个条件分别写SQL 语句来查询。要想知道学生“是否加入了多个社团”，我们需要用HAVING 子句对聚合结果进行判断。

条件1 的SQL -- 条件1 ：选择只加入了一个社团的学生 SELECT std_id, MAX(club_id) AS main_club FROM StudentClub GROUP BY std_id HAVING COUNT(*) = 1;

执行结果1

std_id main_club ------ ---------- 300 4 400 5 500 6 条件2 的SQL -- 条件2 ：选择加入了多个社团的学生 SELECT std_id, club_id AS main_club FROM StudentClub WHERE main_club_flg = 'Y' ;

执行结果2

std_id main_club ------ ---------- 100 1 200 3

这样做也能得到正确的结果，但需要写多条SQL 语句。而如果使用CASE 表达式，下面这一条SQL 语句就可以了。

SELECT std_id, CASE WHEN count(club_id) = 1 THEN 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

这条SQL 语句在CASE 表达式里使用了聚合函数，又在聚合函数里使用了CASE 表达式。这种嵌套的写法让人有点眼花缭乱，其主要目的是用CASE WHEN COUNT(*) = 1 …… ELSE ……. 这样的CASE 表达式来表示“只加入了一个社团还是加入了多个社团”这样的条件分支。我们在初学SQL的时候，都学过对聚合结果进行条件判断时要用HAVING 子句，但从这道例题可以看到，在SELECT 语句里使用CASE 表达式也可以完成同样的工作，这种写法比较新颖。

面试题练习

滴滴2020年面试题：如何找出最小的N个数？

【题目】 “学生表”里记录了学生的学号、入学时间等信息。“成绩表”里是学生选课成绩的信息。两个表中的学号一一对应。

【现在需要】

筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）

分析思路：筛选出计算机专业，所以条件在计算机专业 2017年入学，所以条件还有2017年入学 年龄最小的三位同学，可以用窗口函数排序，取前三。也可以用order by加limit取前三。

-- 解法1 order by 加 limit SELECT 姓名,年龄 FROM 学生表 WHERE EXTRACT(DATE FROM 入学时间) = 2017 AND 专业 in '计算机' ORDER BY 年龄 ASC LIMIT 3 -- 解法2：窗口函数 SELECT 姓名,年龄 FROM ( SELECT 姓名,年龄, RANK_NUMBER() OVER (ORDER BY 年龄 ASC) AS RANK FROM 学生表 WHERE EXTRACT(YEAR FROM 入学时间) = 2017 AND 专业 in '计算机') WHERE RANK < 4 统计每个班同学各科成绩平均分大于80分的人数和人数占比

分析思路：统计每个班同学各科成绩，所以分组条件按课程号 各科成绩平均分大于80分，需要用到AVG() 各科成绩平均分大于80分人数和人数占比，这是典型的分组汇总问题，所以SUM() 加CASE WHEN()。

先求出每个班同学成绩的平均分

SELECT S.姓名,S.班级, AVG(G.分数) AS 各科平均成绩, COUNT(*) AS 总人数 FROM 学生表 S LEFT JOIN 成绩表 G -- 保留学生表中的班级，所以左连 ON S.学号 = G.学号 GROUP BY G.课程号

各科成绩平均分大于80分人数和人数占比：

SELECT A.班级, SUM(CASE WHEN 各科平均成绩 > 80 THEN 1 ELSE 0 END) AS 大于80分人数, SUM(CASE WHEN 各科平均成绩 > 80 THEN 1 ELSE 0 END) / 总人数 AS 人数占比 FROM ( SELECT S.姓名,S.班级, AVG(G.分数) AS 各科平均成绩, COUNT(*) AS 总人数 FROM 学生表 S JOIN 成绩表 G ON S.学号 = G.学号 GROUP BY G.课程号 ) AS A GROUP BY 班级

面试题2：如何分析红包领取情况

【题目】 “用户活跃表”记录了用户的登录信息，包括用户标识、用户登录日期，以及是否是新用户（如果是新注册的用户值为1；如果是老用户，值为0）。 “领取红包表”里记录了用户领取红包的信息，包括抢红包日期、抢红包时间、用户ID、领取红包金额。 【问题】 1.计算2019年6月1日至今，每日DAU（活跃用户是指有登陆的用户）。

分析： 2019年6月1日至今，筛选条件是2019年6月1日至今。 每日DAU，按照每日统计用户数量。

SELECT 登录日期,COUNT(用户ID) AS DAU FROM 用户活跃表 WHERE DATE_FORMAT(登录日期, %Y-%M-%D) BETWEEN 2019-06-01 AND NOW();

2.分析每天领取红包的用户数、人均领取金额、人均领取次数，要考虑用户属性及未登录情况。注意：保留两位小数。

分析： 1.每天领取红包的用户数，按日期分组统计用户ID。 2.人均领取金额，统计每日领取金额总数除以每日总人数。 3.人均领取次数，领取总数除以总人数 4.用户属性：将“每天领取红包的用户”即新用户、老用户、未登录的用户进行区分

SELECT 抢红包日期, COUNT(用户ID) AS 每天领取红包的用户数, SUM(金额) / 每天领取红包的用户数 AS 人均领取金额, SUM(用户ID) / 每天领取红包的用户数 AS 人均领取次数 FROM 领取红包表 GROUP BY 抢红包日期

考虑用户属性，用SUM( CASE WHEN )语句求各用户属性的数量。

SELECT A.抢红包日期, COUNT(DISTINCT A.用户ID) AS 每天领取红包的用户数, SUM(A.金额) / 每天领取红包的用户数 AS 人均领取金额, COUNT(A.用户ID) / 每天领取红包的用户数 AS 人均领取次数, SUM(CASE WHEN B.新用户 = 1 THEN 1 ELSE 0 END) AS 新用户数, SUM(CASE WHEN B.新用户 = 0 THEN 1 ELSE 0 END) AS 老用户数, SUM(CASE WHEN B.新用户 = null THEN 1 ELSE 0 END) AS 未登录用户数, FROM 领取红包表 A LEFT JOIN 用户活跃表 B ON A.用户ID = B.用户ID AND A.抢红包日期 = B.登录日期 GROUP BY A.抢红包日期

大家看看上面写法有什么问题？ 在求取新老用户数的时候没有去重！！！ 如何改进：

SELECT A.抢红包日期, COUNT(DISTINCT A.用户ID) AS 每天领取红包的用户数, SUM(A.金额) / 每天领取红包的用户数 AS 人均领取金额, COUNT(A.用户ID) / 每天领取红包的用户数 AS 人均领取次数, COUNT(DISTINCT CASE WHEN B.新用户 = 1 THEN B.用户ID ELSE null END) AS 新用户数, COUNT(DISTINCT CASE WHEN B.新用户 = 0 THEN B.用户ID ELSE null END) AS 老用户数, COUNT(DISTINCT CASE WHEN B.新用户 = null THEN B.用户ID ELSE null END) AS 未登录用户数, FROM 领取红包表 A LEFT JOIN 用户活跃表 B ON A.用户ID = B.用户ID AND A.抢红包日期 = B.登录日期 GROUP BY A.抢红包日期

3.分析每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数。 分析： 按领取天数区分用户数、金额，那么属于分组汇总问题:

统计每个用户每个月领取红包的天数

SELECT DISTINCT 用户ID, DISTINCT DATE_FORMAT(抢红包日期, %M) AS 月份, --还可使用month(日期)获取月份 COUNT(DISTINCT 抢红包日期) AS 每月抢红包天数, SUM(金额) AS 每月领取总金额 COUNT(*) AS 每月领取总次数 FROM 领取红包表 GROUP BY DATE_FORMAT(抢红包日期, %M)

计算取每个月领取红包的用户数:

SELECT 月份, 每月抢红包天数, COUNT(DISTINCT 用户ID) AS 每个月领取红包的用户数, 每月领取总金额 / 每个月领取红包的用户数 AS 人均领取金额, 每月领取总次数 / 每个月领取红包的用户数 AS 人均领取次数 FROM ( SELECT DISTINCT 用户ID, DISTINCT DATE_FORMAT(抢红包日期, %M) AS 月份, COUNT(DISTINCT 抢红包日期) AS 每月抢红包天数, SUM(金额) AS 每月领取总金额 COUNT(*) AS 每月领取总次数 FROM 领取红包表 GROUP BY DATE_FORMAT(抢红包日期, %M) ) A

若不用子查询：

SELECT MONTH(抢红包日期) AS 月份, COUNT(DISTINCT 抢红包日期) AS 每月抢红包天数, COUNT(DISTINCT 用户ID) AS 每个月领取红包的用户数, SUM(金额)/COUNT(DISTINCT 用户ID) AS 人均领取金额, COUNT(*)/COUNT(DISTINCT 用户ID) AS 人均领取次数 FROM 领取红包表 GROUP MONTH(抢红包日期);

4.分析每个月领过红包用户和未领红包用户的数量。 分析：将两表进行左连接，如果“领取红包”表中的用户为非空，那么该用户是“领过红包用户”（也就是在“领取红包”表中匹配到了数据）；如果“领取红包”表中的用户为空的，那么该用户是“未领取红包用户”（也就是在“领取红包”表没有匹配到数据）。用case语句来判断。

SELECT MONTH(B.抢红包日期) AS 月份, COUNT(DISTINCT CASE WHEN A.用户ID is not null THEN A.用户ID ELSE null END) 领过红包用户数, COUNT(DISTINCT CASE WHEN A.用户ID is null THEN A.用户ID ELSE null END) 未领红包用户数 FROM 用户活跃表 A LEFT JOIN 领取红包表 B ON A.用户ID = B.用户ID AND A.登录日期 = b.抢红包日期 GROUP BY 月份

读到这里的同学，相信大家对于CASE语句有了深刻的认识和掌握了吧。 ———————————————————————————————————————————————— 博主码字不易，大家关注点个赞转发再走呗 ，您的三连是对我创作的最大支持^ - ^ 参考：《SQL进阶教程》