SQL架构
表: 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 分。
- 如果球队输掉了比赛(例如,比对手少进球),就 不得分 。
写出一条SQL语句以查询每个队的 team_id
,team_name
和 num_points
。
返回的结果根据 num_points
降序排序,如果有两队积分相同,那么这两队按 team_id
升序排序。
查询结果格式如下。
示例 1:
输入: 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 | +------------+--------------+---------------+
union all:
select
t.team_id,t.team_name,ifnull(sss1.num_points,0) num_points
from
Teams t left join
(
select
team_id,sum(num_points) num_points
from
(
select
host_team team_id,sum(case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end) num_points
from
Matches
group by
match_id
union all
select
guest_team team_id,sum(case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
when host_goals < guest_goals then 3
end) num_points
from
Matches
group by
match_id
) ss1
group by
team_id
)sss1
on t.team_id = sss1.team_id
order by
num_points desc,team_id
if 嵌套 :
select
team_id, team_name,ifnull(sum(if(team_id=host_team,host_score,guest_score)),0) as num_points
from Teams m
left join
(
select host_team, guest_team,
if(host_goals<guest_goals,0,if(host_goals>guest_goals,3,1)) as host_score,
if(host_goals<guest_goals,3,if(host_goals>guest_goals,0,1)) as guest_score
from Matches) t
on m.team_id = t.host_team or m.team_id = t.guest_team
group by team_id
order by num_points desc, team_id