Mysql case when用法——以查询球队积分为例

以查询球队积分为例分析Mysql中case when的用法

 

问题描述

 
Teams表：

Column Name	Type
team_id	int
team_name	varchar

 此表的主键是 team_id，表中的每一行都代表一支独立足球队。
 
Matches表

Column Name	Type
match_id	int
host_team	int
guest_team	int
host_goals	int
guest_goals	int

 此表的主键是 match_id，表中的每一行都代表一场已结束的比赛，比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。
 
 积分规则：

• 赢一场得3分
• 平局得1分
• 输一场得0分

 写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。
 

问题示例

 

Teams table:

team_id	team_name
10	Leetcode FC
20	NewYork FC
30	Atlanta FC
40	Chicago FC
50	Toronto FC

 
Matches table:

match_id	host_team	guest_team	host_goals	guest_goals
1	10	20	3	0
2	30	10	2	2
3	10	50	5	1
4	20	30	1	0
5	50	30	1	0

 
Result table:

team_id	team_name	num_points
10	Leetcode FC	7
20	NewYork FC	3
50	Toronto FC	3
30	Atlanta FC	1
40	Chicago FC	0

 

问题求解

 

select team_id, team_name, 
sum(num_points) as num_points
from (
    select *
    from (
        select t1.team_id, t1.team_name,
        sum(
            case
            when m1.host_goals > m1.guest_goals then 3
            when m1.host_goals = m1.guest_goals then 1
            when m1.host_goals < m1.guest_goals then 0
            when m1.host_goals is null then 0
            end
        ) as num_points
        from teams as t1
        left join matches as m1
        on t1.team_id = m1.host_team
        group by t1.team_id
    ) as t 
    union all
    select *
    from (
        select t2.team_id, t2.team_name,
        sum(
            case
            when m2.host_goals < m2.guest_goals then 3
            when m2.host_goals = m2.guest_goals then 1
            when m2.host_goals > m2.guest_goals then 0
            when m2.guest_goals is null then 0
            end
        ) as num_points
        from teams as t2
        left join matches as m2
        on t2.team_id = m2.guest_team
        group by t2.team_id
    ) as tt
    ) as ttt
group by team_id
order by num_points desc, team_id asc

 

问题分析

 
 此问题首先要分为主场和客场，对这两种情况分别查询，然后用union all进行连接。
 
 每种情况中，用sum(case when)进行判断并求和，注意由于用的是左连接（因为有的队没有主场或客场），因此要加上空值的判断when m1.host_goals is null then 0，最后别忘了用end
 
 在将两种情况分别判断并连接后，要再用一次查询，最后再按照顺序要求进行排序。要注意的是，由于涉及到的表比较多，但每个表都要记得起别名。