1212. 查询球队积分

select t.team_id,t.team_name,
sum(case when m.host_goals > m.guest_goals then 3 
      when m.host_goals = m.guest_goals then 1
      else 0 end) num_points
from teams t 
left join
(select host_team, guest_team, host_goals, guest_goals
from matches
union all
select guest_team host_team, host_team guest_team, guest_goals host_goals, host_goals guest_goals
from matches) m
on m.host_team = t.team_id
group by t.team_id
order by num_points desc, t.team_id asc

---

这种不用union all 但是要多想一下= =想不清楚很容易错 

select t.team_id,t.team_name,sum(
    CASE WHEN t.team_id= m.host_team and m.host_goals>m.guest_goals THEN 3
         WHEN m.host_goals = m.guest_goals THEN 1
         WHEN t.team_id = m.guest_team and m.guest_goals>m.host_goals THEN 3 ELSE 0 END
    ) as num_points 
from Teams t 
left join Matches m 
on t.team_id=m.host_team or t.team_id=m.guest_team 
group by t.team_id 
order by num_points desc ,team_id asc