【Leetcode】SQL查询球队积分

方法1：步骤拆分

select t.team_id team_id,t.team_name team_name,ifnull(score,0) num_points
from
(
select 
team_id,sum(score) score    #3.union  联合起来
 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      #1.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  #2.客队的得分
) b
group by team_id
)a right join teams t on a.team_id=t.team_id  #4. 外连接
order by num_points desc,t.team_id asc;