Medium之1212.查询球队积分
Table: Teams
| Column Name | Type |
|---|---|
| team_id | int |
| team_name | varchar |
此表的主键是 team_id，表中的每一行都代表一支独立足球队。
Table: 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 分别表示。
问题
写出一条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 |
来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/team-scores-in-football-tournament
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
解答
法一：UNION ALL
通过union all 把team_id=guest_team的分数拼接到team_id=host_team的后面构成新表，然后group by team_id
select team_id,team_name,sum(case when host_goals>guest_goals then 3when host_goals=guest_goals then 1else 0end) num_points
from teams tleft join (select host_team,guest_team,host_goals,guest_goalsfrom matchesunion allselect guest_team host_team,host_team guest_team,guest_goals host_goals,host_goals guest_goalsfrom matches) tmp
on t.team_id = tmp.host_team
group by team_id
order by num_points desc,team_id
法二：CASE WHEN罗列大法
通过控制team_id是等于host_team还是guest_team来罗列分成六种情况讨论，要注意，也要考虑team_id不在（host_team,guest_team)中的情况，则要通过ifnull(,0)进行控制。
select team_id,team_name,ifnull(sum(case when host_goals>guest_goals and team_id=host_team then 3when host_goals=guest_goals and team_id=host_team then 1when host_goals<guest_goals and team_id=host_team then 0when host_goals<guest_goals and team_id=guest_team then 3when host_goals=guest_goals and team_id=guest_team then 1when host_goals>guest_goals and team_id=guest_team then 0end),0) as num_points
from teams,matches
group by team_id
order by num_points desc,team_id
法三：CASE WHEN中嵌套IF语句
法三的基本思想和法二类似，但是巧妙的是，对于平局的情况，不需要区分team_id=host_team还是guest_team，所以作为case when 的else情况，同时还要注意team_id是否在（host_team,guest_team)中的情况
select team_id,team_name,sum(case when host_goals>guest_goals then if(team_id = host_team,3,0)when host_goals<guest_goals then if(team_id = guest_team,3,0)else if(team_id in (host_team,guest_team),1,0)end) as num_points
from teams ,matches
group by team_id
order by num_points desc,team_id
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！