Mysql中的Case语句

1.Case基本初识

case语句是mysql中的一个条件语句，可以在字段中使用case语句进行复杂的筛选以及构造新的字段。下面通过两个leetcode例子来详细解读case语句的功能：

1.1 Leetcode 627 (Easy) 交换工资

这道题目要求我们将sex字段的m和f交换，所以可以使用case语句进行条件判断并赋给新的值。

# Write your MySQL query statement below
update salary set sex = (
    case sex 
    when "m" then "f"
    else "m"
    end
);

1.2 Leetcode 626 (Medium) 换座位

这道题目要求我们换相邻两个同学的座位，所以可以交换id，使用case语句进行条件判断之后并赋给新的值。

# Write your MySQL query statement below
select (case 
        when id%2 != 0 and id != (select count(id) as counts from seat) then id+1 
        when id%2 != 0 and id = counts then id
        when id%2 =0 then id - 1
        end
) as id, student from seat inner join (select count(id) as counts from seat) as seatcounts order by id;

注意此时的case后面没有接任何字段，而是在when的判断条件进行字段的控制筛选。

1.3 case语句的基本语法

我们将上述两道leetcode题目进行以下case语法进行以下总结：

# 对应于627
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

# 对应于626
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

2.Case语句进阶实战

2.1 热身题目：列转行操作

有一个成绩表scores，想要实现每个学生的每门成绩分别是多少？

scores表的结构如下：

id	name	course	score
1	二狗	math	98
2	二狗	chinese	99
3	二狗	english	97
4	小白	math	78
5	小白	chinese	69
6	小白	english	84

想要按照以下的结构输出，如何编写sql代码呢？

name	math	chinese	english
二狗	98	99	97
小白	78	69	84

首先我们看到输出表的结构是以name进行分组，可以使用group by语句，写出如下的格式：

select name from scores group by name;

其次需要思考如何把course的字段按照类别拆解成几个字段呢，这时候就需要使用case语句进行条件控制判断：

select name, 
(case course when "math" then score else 0) as math,
(case course when "chinese" then score else 0) as chinese,
(case course when "english" then score else 0) as english
from scores;

通过上述语句的输出如下：

name	math	chinese	english
二狗	98	0	0
二狗	0	99	0
二狗	0	0	97
小白	78	0	0
小白	0	69	0
小白	0	0	84

最后通过上述两步骤整合，得到的代码如下：

select name, 
sum(case course when "math" then score else 0) as math,
sum(case course when "chinese" then score else 0) as chinese,
sum(case course when "english" then score else 0) as english
from scores group by name;

2.2 滴滴笔试题目（2021校招 DS数据分析试卷）

付费统计：

现有一个数据表t_user_payment，包含乘客的支付信息，其中有四个字段，id(string)订单id，amount(int)支付金额，order_type(string)订单类别，payment_chnl(string)支付渠道。

现在要依据不同的支付渠道统计以下三种订单类型（kuaiche，shunfengche，zhuanche）的总支付金额，最终要包含四个字段payment_chnl，kuaiche_amt，shunfengche_amt，zhuanche_amt，按照payment_chnl正序排序。当某种payment_chnl下无某种order_type订单时，对应的_amt取0。

样例输入描述：

id	amount	order_type	payment_chnl
1	10	kuaiche	wechat
2	13	kuaiche	alipay
3	17	kuaiche	other
4	15	shunfengche	alipay
5	38	shunfengche	wechat
6	20	zhuanche	other
7	9	kuaiche	alipay
8	25	shunfengche	wechat

样例输出描述：

payment_chnl	kuaiche_amt	shunfengche_amt	zhuanche_amt
alipay	21	15	0
wechat	10	63	0
other	17	0	20

解答：显然这是一个列转行的问题，所以直接考虑使用case语句和group by：

select payment_chnl, 
sum(case order_type when "kuaiche" then amount else 0) as kuaiche_amt,
sum(case order_type when "shunfengche" then amount else 0) as shunfengche_amt,
sum(case order_type when "zhuanche" then amount else 0) as zhuanche_amt,
from t_user_payment group by payment_chnl;

2.3 Leetcode 262 （Hard）行程和用户

这道题目是出行领域的实际业务问题，比常规的题目要更复杂，因此需要读者更加细心。

首先，需要利用自联结筛选出没有被禁止的用户（司机和乘客）

select id, t.status, request_at
from trips as t 
inner join users as u1 on u1.users_id = t.client_id and u1.banned = "No"
inner join users as u2 on u2.users_id = t.driver_id and u2.banned = "No";

其次，使用case语句对取消的订单记为1，未取消的订单记为0。

最后，计算出取消订单率并筛选日期条件。

select request_at as Day,
round(sum(case when status != "completed" then 1 else 0 end)/count(status), 2) as "Cancellation Rate"
from trips as t 
inner join users as u1 on u1.users_id = t.client_id and u1.banned = "No"
inner join users as u2 on u2.users_id = t.driver_id and u2.banned = "No"
where date(t.request_at) between "2013-10-1" and "2013-10-3"
group by t.request_at;

注意，自联结的形式和表的别名。