题62:
根据下表写出一条SQL语句以查询每个队的 team_id,team_name 和 num_points。结果根据 num_points 降序排序,如果有两队积分相同,那么这两队按 team_id 升序排序。
积分要求:
(1)赢一场得三分;
(2)平一场得一分;
(3)输一场不得分。
其中:
- Teams表:主键是 team_id,表中的每一行都代表一支独立足球队;
- Matches表:主键是 match_id,表中的每一行都代表一场已结束的比赛,比赛的主客队分别由它们自己的 id 表示,他们的进球由 host_goals 和 guest_goals 分别表示。
解题思路:
(1)求出比赛中主场球队的分数;
select host_team team_id,
sum(case
when host_goals>guest_goals then 3
when host_goals<guest_goals then 0
else 1
end) score
from matches
group by host_team
(2)求出比赛中客场球队的分数,然后用union all合并第一步;
select guest_team team_id,
sum(case
when host_goals>guest_goals then 0
when host_goals<guest_goals then 3
else 1
end) score
from matches
group by guest_team
(3)此时联合查询查出来的表是参赛的所有球队的总分,没参赛的也要算,所以这里用外连接;
(4)排序,具体代码如下:
select t.team_id, t.team_name, IFNULL(score,0) num_points
from
(
select team_id, SUM(score) score
from (
select host_team team_id,
sum(case
when host_goals>guest_goals then 3
when host_goals<guest_goals then 0
else 1
end) score
from matches
group by host_team
union all
select guest_team team_id,
sum(case
when host_goals>guest_goals then 0
when host_goals<guest_goals then 3
else 1
end) score
from matches
group by guest_team
) b
group by team_id
) a
right join teams t on t.team_id=a.team_id
order by num_points desc, t.team_id;