继续学习CASE表达式的实用的场景案例
在CASE表达式中使用聚合函数
在CASE表达式中使用聚合函数最常见的一种场景就是按照某个字段分组后，然后根据条件统计每组中相关数据，假设这里有一张显示了学生及其加入的社团的一览表，数据如下：
1 100 1 棒球 Y
2 100 2 管弦乐 N
3 200 2 管弦乐 N
有的学生同时加入了多个社团（如学号为100的学生），有的学生只加入了某一个社团（如学号为200的学生）。对于加入了多个社团的学生，通过将其主社团标志列设置为Y来表明是主社团；对于只加入了一个社团的学生，将其主社团标志列设置为N。
我们按照下面的条件查询这张表里的数据：
- 获取只加入了一个社团的学生的社团ID
- 获取加入了多个社团的学生的主社团ID
语句如下：
SELECT std_id,
CASE WHEN COUNT(＊) = 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;
行程和用户
CASE WHEN语句总结的差不多了，拿leetcode上一道题练练手，如下：
表trips:
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | date |
+-------------+----------+
id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表：Users:
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id 是这张表的主键。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
这是leetcode数据库第262题。这道题可以用case when语句完美解决，如下：
WITH cte AS
(SELECT t.request_at,
sum(CASE
WHEN (t.STATUS = 'cancelled_by_driver' OR t.STATUS = 'cancelled_by_client')
AND EXISTS (SELECT 1
FROM Users u1
WHERE (u1.users_id = t.client_id)
AND u1.banned = 'No')
AND EXISTS (SELECT 1
FROM Users u1
WHERE (u1.users_id = t.driver_id)
AND u1.banned = 'No') THEN
1
ELSE
0
END) cancle_num,
sum(CASE
WHEN (NOT EXISTS (SELECT 1
FROM Users u1
WHERE (u1.users_id = t.client_id)
AND u1.banned = 'Yes') AND NOT EXISTS
(SELECT 1
FROM Users u1
WHERE (u1.users_id = t.driver_id)
AND u1.banned = 'Yes')) THEN
1
ELSE
0
END) complete_num
FROM Trips t
where t.request_at <= '2013-10-03'
and t.request_at >= '2013-10-01'
GROUP BY t.request_at)
SELECT c.request_at 'Day', CONVERT((c.cancle_num / c.complete_num), DECIMAL(15, 2)) 'Cancellation Rate'
FROM cte c
where c.complete_num != 0
当然， 还有更完美更好的方式，case when只是其中一种。
通过一些案例，领略了CASE表达式的灵活和强大的表达能力。CASE表达式是支撑SQL声明式编程的根基之一，也是灵活运用SQL时不可或缺的基础技能。
完结！
