LeetCode 1212 查询球队积分(PostgreSQL)

数据准备

Create table If Not Exists Teams (team_id int, team_name varchar(30))
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int)
Truncate table Teams
insert into Teams (team_id, team_name) values ('10', 'Leetcode FC')
insert into Teams (team_id, team_name) values ('20', 'NewYork FC')
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC')
insert into Teams (team_id, team_name) values ('40', 'Chicago FC')
insert into Teams (team_id, team_name) values ('50', 'Toronto FC')
Truncate table Matches
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0')

需求

如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
如果双方打成平手(即，与对方得分相同)，则得 1 分。
如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。

输入

输出

1.查詢三種情況下的host_team,及其得分

select host_team ,
case 
	when host_goals > guest_goals then 3 
	when host_goals < guest_goals then 0
	else 1
end as team_goals
from matches;

2.查詢平局的guest_team及其得分

select guest_team , 1 as team_goals
from matches
where host_goals = guest_goals;

3.拼接兩種條件下的結果,創建臨時表,統計每個球隊總得分

with t1 as (
	select host_team ,
	case 
		when host_goals > guest_goals then 3 
		when host_goals < guest_goals then 0
		else 1
	end as team_goals
	from matches
	union all
	select guest_team , 1 as team_goals
	from matches
	where host_goals = guest_goals
),t2 as (
select host_team,sum(team_goals) goals_sum
from t1
group by host_team
)
select team_id,team_name,coalesce(goals_sum,0) as num_points
from teams left join t2
on teams.team_id =t2.host_team
order by num_points desc,team_id
;