力扣数据库进阶50--No.1212题解

官方难度中等，体感稍繁琐，需要处理细节。

先在赛事表里，分开计算主客队的得分，这里用到case when。

再将主队、客队union all合并在一起，为了方便后面做group by聚合求和。

由于缺乏队伍id，再联表left join取到该信息，同时用ifnull处理未比赛的队伍。

with tmp1 as
(select *,
    case when host_goals > guest_goals then 3 
        when host_goals = guest_goals then 1
        else 0 end as host_points,
    case when host_goals < guest_goals then 3 
        when host_goals = guest_goals then 1
        else 0 end as guest_points
from Matches)

,tmp2 as
((select host_team as team,host_points as points
from tmp1)
union all
(select guest_team as team,guest_points as points
from tmp1))

select team_id,team_name,ifnull(sum(points),0) as num_points
from Teams left join tmp2
on Teams.team_id = tmp2.team
group by team_name
order by num_points desc,team_id

看了下官方题解，也采用了这种方法。