描述
表:
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升序排序。返回结果格式如下。
示例 1:
输入： Teamstable: +-----------+--------------+ | 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 | +------------+--------------+---------------+
数据准备
Create table If Not Exists Teams (team_id int, team_name varchar(30))
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int)
Truncate table Teams
insert into Teams (team_id, team_name) values ('10', 'Leetcode FC')
insert into Teams (team_id, team_name) values ('20', 'NewYork FC')
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC')
insert into Teams (team_id, team_name) values ('40', 'Chicago FC')
insert into Teams (team_id, team_name) values ('50', 'Toronto FC')
Truncate table Matches
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0')分析
法一：
①先计算出主队得分和客队得分情况select host_team, guest_team, host_goals, guest_goals, case when host_goals > guest_goals then 3 when host_goals = guest_goals then 1 else 0 end host_points, case when host_goals < guest_goals then 3 when host_goals = guest_goals then 1 else 0 end guest_points from matches
②然后根据主队、客队得分情况求和
with t1 as (select host_team, guest_team, host_goals, guest_goals, case when host_goals > guest_goals then 3 when host_goals = guest_goals then 1 else 0 end host_points, case when host_goals < guest_goals then 3 when host_goals = guest_goals then 1 else 0 end guest_points from matches) select host_team, sum(host_points) points1 from t1 group by host_team union all select guest_team, sum(guest_points) points1 from t1 group by guest_team
③连接两张表 根据球队分组 求总共的分数 此处是用teams左连接 获取到所有球队信息
用ifnull函数 先对分数判空 如果为空则赋0
select team_id, team_name, sum(ifnull(points1, 0)) num_points from teams left join t2 on teams.team_id = t2.host_team group by team_id, team_name order by num_points desc, team_id
法二：
①先连接两张表 条件是 team_id = host_team or team_id = guest_team 并且为左连接
select team_id,team_name from Teams left join matches on team_id = host_team or team_id = guest_team
②对各个球队得分进行定义
如果是根据主队id关联 那么主队赢了积三分
如果是根据客队id关联 那么客队赢了积三分
如果 主队得分=客队 那么不论根据什么关联 都积一分
否则就是零分
select team_id,team_name , case when team_id = host_team and host_goals > guest_goals then 3 when host_goals = guest_goals then 1 when team_id = guest_team and host_goals < guest_goals then 3 else 0 end num_points from Teams left join matches on team_id = host_team or team_id = guest_team
③然后根据上一步结果 进行分组排序
select team_id,team_name , sum(case when team_id = host_team and host_goals > guest_goals then 3 when host_goals = guest_goals then 1 when team_id = guest_team and host_goals < guest_goals then 3 else 0 end )num_points from Teams left join matches on team_id = host_team or team_id = guest_team group by team_id, team_name order by num_points desc,team_id
代码
# 法一
with t1 as (select host_team,
guest_team,
host_goals,
guest_goals,
case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0 end host_points,
case
when host_goals < guest_goals then 3
when host_goals = guest_goals then 1
else 0 end guest_points
from matches)
, t2 as (select host_team, sum(host_points) points1
from t1
group by host_team
union all
select guest_team, sum(guest_points) points1
from t1
group by guest_team)
select team_id, team_name, sum(ifnull(points1, 0)) num_points
from teams
left join t2
on teams.team_id = t2.host_team
group by team_id, team_name
order by num_points desc, team_id;
# 法二
select team_id,team_name ,
sum(case
when team_id = host_team and host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when team_id = guest_team and host_goals < guest_goals then 3
else 0 end )num_points
from Teams left join matches on team_id = host_team or team_id = guest_team
group by team_id, team_name
order by num_points desc,team_id总结
法一是先判断后连接
法二是先连接后判断
关键点是 两张表关联条件 和 赋分情况的考虑
