1212

 

 

 

 方法一：我自己写的

# with t as
# (
# select *
# ,case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 else 0 end as host_score,
# case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 else 0 end as guest_score
# from Matches
# )


# select team_id,team_name,ifnull(num_points,0) num_points
# from Teams
# left join 
# (
# select host_team,sum(score) num_points
# from 
# (
# select host_team,sum(host_score) score
# from t
# group by host_team
# union all
# select guest_team,sum(guest_score) score
# from t
# group by guest_team    
# ) a
# group by host_team
# ) b 
# on Teams.team_id=b.host_team
# order by num_points desc,team_id;

方法二：在方法一上面进行简化

select team_id,team_name,ifnull(sum(score),0) num_points
from Teams
left join
(
select host_team,
case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 else 0 end as score
from Matches
union all
select guest_team,
case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 else 0 end as score
from Matches
) a
on Teams.team_id=a.host_team
group by team_id
order by num_points desc, team_id;

 注意：sum函数不可以把null转换为0，count(字段名） 可以把null转换为0。

方法三：一个查询搞定

select team_id,team_name,
ifnull(sum(case when team_id=host_team and host_goals>guest_goals then 3 
when team_id=host_team and host_goals=guest_goals then 1
when team_id=host_team and host_goals<guest_goals then 0 
when team_id=guest_team and host_goals<guest_goals then 3 
when team_id=guest_team and host_goals=guest_goals then 1
when team_id=guest_team and host_goals>guest_goals then 0 end
),0) num_points
from Teams
left join Matches
on Teams.team_id=Matches.host_team or Teams.team_id=Matches.guest_team
group by team_id
order by num_points desc, team_id asc;

方法四：一个查询搞定（笛卡尔积)

# SELECT team_id, team_name, 
#     sum(case 
#     when host_goals>guest_goals then if(team_id = host_team,3,0)
#     when host_goals<guest_goals then if(team_id = guest_team,3,0) 
#     else if(team_id in(host_team,guest_team),1,0) end)  
#     num_points
# from Teams t, Matches m 
# GROUP by team_id,team_name
# ORDER BY num_points desc, team_id;

注意：方法的原理不懂