1 题目描述
表: 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 分。
- 如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
编写解决方案，以找出每个队的 team_id，team_name 和 num_points。
返回的结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。
2 测试用例
输入：
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 |
+------------+--------------+---------------+-------------+--------------+
输出：
+------------+--------------+---------------+
| 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 |
+------------+--------------+---------------+
3 解题思路
3.1 解法1：按照host_team和guest_team分组统计后合并结果
- 对
host_team分组统计得分情况
select host_team as team_id,
sum(case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0
end) as team_goals
from Matches
group by host_team
执行结果
+-------+----------+
|team_id|team_goals|
+-------+----------+
|10 |6 |
|30 |1 |
|20 |3 |
|50 |3 |
+-------+----------+
- 对
guest_team分组统计得分情况
select guest_team as team_id,
sum(case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
else 3
end) as team_goals
from Matches
group by guest_team
执行结果
+-------+----------+
|team_id|team_goals|
+-------+----------+
|20 |0 |
|10 |1 |
|50 |0 |
|30 |0 |
+-------+----------+
- 将步骤1和2的结果进行全连接
union all
select host_team as team_id,
sum(case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0
end) as team_goals
from Matches
group by host_team
union all
select guest_team as team_id,
sum(case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
else 3
end) as team_goals
from Matches
group by guest_team
执行结果
+-------+----------+
|team_id|team_goals|
+-------+----------+
|10 |6 |
|30 |1 |
|20 |3 |
|50 |3 |
|20 |0 |
|10 |1 |
|50 |0 |
|30 |0 |
+-------+----------+
- 将
Teams左外连接步骤3的结果， 并分组统计各球队的得分总计group by t.team_id, t.team_name, 对没有比赛记录的球队将得分设置为0，ifnull(sum(team_goals), 0)
select t.team_id, t.team_name, ifnull(sum(team_goals), 0) as num_points
from Teams as t
left join (select host_team as team_id,
sum(case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0
end) as team_goals
from Matches
group by host_team
union all
select guest_team as team_id,
sum(case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
else 3
end) as team_goals
from Matches
group by guest_team) as m on t.team_id = m.team_id
group by t.team_id, t.team_name
执行结果
+-------+-----------+----------+
|team_id|team_name |num_points|
+-------+-----------+----------+
|10 |Leetcode FC|7 |
|20 |NewYork FC |3 |
|30 |Atlanta FC |1 |
|40 |Chicago FC |0 |
|50 |Toronto FC |3 |
+-------+-----------+----------+
- 按照题目要求对步骤4的结果进行数据排序
order by num_points desc, t.team_id asc
select t.team_id, t.team_name, ifnull(sum(team_goals), 0) as num_points
from Teams as t
left join (select host_team as team_id,
sum(case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0
end) as team_goals
from Matches
group by host_team
union all
select guest_team as team_id,
sum(case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
else 3
end) as team_goals
from Matches
group by guest_team) as m on t.team_id = m.team_id
group by t.team_id, t.team_name
order by num_points desc, t.team_id asc;
执行结果
+-------+-----------+----------+
|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 |
+-------+-----------+----------+
3.1.1 踩坑记录
步骤3中使用的是union all，最开始使用的是union， 部分测试用例是能通过的， 但是有一个测试用例不通过
测试用例数据
Teams =
| team_id | team_name |
| ------- | ----------- |
| 10 | Leetcode FC |
| 20 | NewYork FC |
| 30 | Atlanta FC |
Matches =
| match_id | host_team | guest_team | host_goals | guest_goals |
| -------- | --------- | ---------- | ---------- | ----------- |
| 1 | 10 | 20 | 0 | 0 |
| 2 | 10 | 30 | 2 | 2 |
| 3 | 20 | 30 | 1 | 1 |
预期结果
| team_id | team_name | num_points |
| ------- | ----------- | ---------- |
| 10 | Leetcode FC | 2 |
| 20 | NewYork FC | 2 |
| 30 | Atlanta FC | 2 |
使用union执行的结果如下
| team_id | team_name | num_points |
| ------- | ----------- | ---------- |
| 10 | Leetcode FC | 2 |
| 30 | Atlanta FC | 2 |
| 20 | NewYork FC | 1 |
原因是分别统计host_team和guest_team时，都存在一条team_id = 20, team_goals = 1的数据，使用union时将两条记录合并到一条了
3.2 解法2：left join + case when
- 将
Teams表左外连接Matches，连接条件m.host_team = t.team_id or m.guest_team = t.team_id
select t.*,
m.*
from Teams as t
left join Matches as m
on m.host_team = t.team_id or m.guest_team = t.team_id
执行结果
+-------+-----------+--------+---------+----------+----------+-----------+
|team_id|team_name |match_id|host_team|guest_team|host_goals|guest_goals|
+-------+-----------+--------+---------+----------+----------+-----------+
|10 |Leetcode FC|3 |10 |50 |5 |1 |
|10 |Leetcode FC|2 |30 |10 |2 |2 |
|10 |Leetcode FC|1 |10 |20 |3 |0 |
|20 |NewYork FC |4 |20 |30 |1 |0 |
|20 |NewYork FC |1 |10 |20 |3 |0 |
|30 |Atlanta FC |5 |50 |30 |1 |0 |
|30 |Atlanta FC |4 |20 |30 |1 |0 |
|30 |Atlanta FC |2 |30 |10 |2 |2 |
|40 |Chicago FC |null |null |null |null |null |
|50 |Toronto FC |5 |50 |30 |1 |0 |
|50 |Toronto FC |3 |10 |50 |5 |1 |
+-------+-----------+--------+---------+----------+----------+-----------+
- 对
team_id,team_name分组统计，当t.team_id = m.host_team则计算球队在host_team的分数，当t.team_id = m.m.host_team则计算球队在guest_team的分数，再对得分情况进行sum统计, 对没有比赛记录的球队的总共得分记录为0
select team_id,
team_name,
ifnull(sum(
case
when t.team_id = m.host_team and m.host_goals > m.guest_goals then 3
when t.team_id = m.host_team and m.host_goals < m.guest_goals then 0
when t.team_id = m.guest_team and m.host_goals < m.guest_goals then 3
when t.team_id = m.guest_team and m.host_goals > m.guest_goals then 0
when m.host_goals = m.guest_goals then 1
end
), 0) as num_points
from Teams as t
left join Matches as m
on m.host_team = t.team_id or m.guest_team = t.team_id
group by t.team_id, t.team_name
执行情况
+-------+-----------+----------+
|team_id|team_name |num_points|
+-------+-----------+----------+
|10 |Leetcode FC|7 |
|20 |NewYork FC |3 |
|30 |Atlanta FC |1 |
|40 |Chicago FC |0 |
|50 |Toronto FC |3 |
+-------+-----------+----------+
- 对步骤2的结果，按照题目要求进行排序
select team_id,
team_name,
ifnull(sum(
case
when t.team_id = m.host_team and m.host_goals > m.guest_goals then 3
when t.team_id = m.host_team and m.host_goals < m.guest_goals then 0
when t.team_id = m.guest_team and m.host_goals < m.guest_goals then 3
when t.team_id = m.guest_team and m.host_goals > m.guest_goals then 0
when m.host_goals = m.guest_goals then 1
end
), 0) as num_points
from Teams as t
left join Matches as m
on m.host_team = t.team_id or m.guest_team = t.team_id
group by t.team_id, t.team_name
order by num_points desc, team_id
执行结果
+-------+-----------+----------+
|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 |
+-------+-----------+----------+
