方法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;