力扣训练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 三、建表语句

1. Create table If Not Exists Teams (team_id int, team_name varchar(30));
2. Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
3. Truncate table Teams;
4. insert into Teams (team_id, team_name) values ('10', 'Leetcode FC');
5. insert into Teams (team_id, team_name) values ('20', 'NewYork FC');
6. insert into Teams (team_id, team_name) values ('30', 'Atlanta FC');
7. insert into Teams (team_id, team_name) values ('40', 'Chicago FC');
8. insert into Teams (team_id, team_name) values ('50', 'Toronto FC');
9. Truncate table Matches;
10. insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0');
11. insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2');
12. insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1');
13. insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0');
14. 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 分情况盘算胜场宁静手的得分，再将结果联合起来，代码与结果如下

1. select
2. host_team as team_id,
3. sum(case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
4. from
5. Matches
6. group by host_team
7. union all
8. select
9. guest_team as team_id,
10. sum(case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
11. from
12. Matches
13. group by guest_team

复制代码

team_idnum_points10630120350320null10150null30null 2、思路1代码实现

1. with t1 as(select
2. host_team as team_id,
3. sum(case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
4. from
5. Matches
6. group by host_team
7. union all
8. select
9. guest_team as team_id,
10. sum(case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 end) as num_points
11. from
12. Matches
13. group by guest_team
14. )selectTeams.team_id,team_name,ifnull(sum(num_points),0) as num_pointsfrom t1 right join Teamson t1.team_id=Teams.team_idgroup  by 1,2order by 3 desc,1 asc;

复制代码

team_idteam_namenum_points10Leetcode FC720NewYork FC350Toronto FC330Atlanta FC140Chicago FC0 3、思路2讲解

上面思路代码比较冗长，而且是先盘算再连接，这时间可以思量先连接再盘算，参考官方博客，之前没想到join 连接不但有and 还有 or ，使用or连接可以就可以将id作为主队和客队都包括在内

1. SELECT
2.    *
3. FROM Teams
4. LEFT JOIN Matches
5. 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代码实现

1. SELECT
2.     T.team_id ,
3.     T.team_name,
4.     SUM(CASE WHEN T.team_id=M.host_team and host_goals>guest_goals then 3
5.              WHEN T.team_id=M.host_team and host_goals=guest_goals then 1
6.              WHEN T.team_id=M.guest_team and host_goals<guest_goals then 3
7.              WHEN T.team_id=M.guest_team and host_goals=guest_goals then 1
8.              ElSE 0 END) as num_points
9. FROM Teams T
10. LEFT JOIN Matches M
11. ON(M.host_team=T.team_id OR M.guest_team=T.team_id)
12. GROUP BY T.team_id
13. ORDER BY num_points desc,T.team_id asc

复制代码

五、知识总结

①join连接的时间还可以使用or连接，可以达到类似于union all 的连接结果
②group by 和 order by 可以简化，使用 1,2，3l来指代select中的第一个、第二个和第三个列，能进一步优化代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告