力扣训练1212.查询球队积分
一、题目链接1212.查询球队积分
二、题目形貌
表: 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:
输入：
Teams table:
team_idteam_name10Leetcode FC20NewYork FC30Atlanta FC40Chicago FC50Toronto FC Matches table:
match_idhost_teamguest_teamhost_goalsguest_goals11020302301022310505142030105503010 输出：
team_idteam_namenum_points10Leetcode FC720NewYork FC350Toronto FC330Atlanta FC140Chicago FC0 三、建表语句
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');
四、题目解答
1、思路1讲解
题目自己并不复杂，但是有主队和客队两种情况，以是思量使用union all先分别盘算按照主队得分和客队得分的情况，使用case when 分情况盘算胜场宁静手的得分，再将结果联合起来，代码与结果如下
select
host_team as team_id,
sum(case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
from
Matches
group by host_team
union all
select
guest_team as team_id,
sum(case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
from
Matches
group by guest_team
team_idnum_points10630120350320null10150null30null 2、思路1代码实现
with t1 as(select
host_team as team_id,
sum(case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
from
Matches
group by host_team
union all
select
guest_team as team_id,
sum(case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
from
Matches
group by guest_team
)selectTeams.team_id,team_name,ifnull(sum(num_points),0) as num_pointsfrom t1 right join Teamson t1.team_id=Teams.team_idgroupby 1,2order by 3 desc,1 asc; team_idteam_namenum_points10Leetcode FC720NewYork FC350Toronto FC330Atlanta FC140Chicago FC0 3、思路2讲解
上面思路代码比较冗长，而且是先盘算再连接，这时间可以思量先连接再盘算，参考官方博客，之前没想到join 连接不但有and 还有 or ，使用or连接可以就可以将id作为主队和客队都包括在内
SELECT
*
FROM Teams
LEFT JOIN Matches
ON Matches.host_team = team_id OR Matches.guest_team = team_id;
match_idhost_teamguest_teamhost_goalsguest_goals11020302301022310505142030105503010 然后使用case when分情况统计，好比 host_team = team_id AND host_goals > guest_goals时，这是作为主队胜利的得分情况，最后关键点在于要按照Teams的team_id去进行group by 分组
4、思路2代码实现
SELECT
T.team_id ,
T.team_name,
SUM(CASE WHEN T.team_id=M.host_team and host_goals>guest_goals then 3
WHEN T.team_id=M.host_team and host_goals=guest_goals then 1
WHEN T.team_id=M.guest_team and host_goals<guest_goals then 3
WHEN T.team_id=M.guest_team and host_goals=guest_goals then 1
ElSE 0 END) as num_points
FROM Teams T
LEFT JOIN Matches M
ON(M.host_team=T.team_id OR M.guest_team=T.team_id)
GROUP BY T.team_id
ORDER BY num_points desc,T.team_id asc
五、知识总结
①join连接的时间还可以使用or连接，可以达到类似于union all 的连接结果
②group by 和 order by 可以简化，使用 1,2，3l来指代select中的第一个、第二个和第三个列，能进一步优化代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]