我有一张桌子,上面有足球比赛。
桌上游戏:
id
team_home (varchar)
team_away (varchar)
team_home_goals INT
team_away_goals INT
要插入我这样做:
insert into games values(null, 'Liverpool', 'Chelsea', 0, 0);
insert into games values(null, 'Arsenal', 'City', 1, 2);
insert into games values(null, 'ManUTD', 'Tottenham', 2, 1);
insert into games values(null, 'Chelsea', 'Arsenal', 0, 0);
insert into games values(null, 'Tottenham', 'Liverpool', 1, 0);
insert into games values(null, 'City', 'ManUTD', 2, 1);
我需要创建一个查询来获取包含位置的表。
根据上面的插入,表格将是:
1 - City 6 points
2 - ManUTD 3 points
3 - Tottenham 3 points
4 - Chelsea 2 points
5 - Liverpool 1 point
6 - Arsenal 1 point
我为此创建的查询是针对“主队”或“客队”。
我可以运行这个查询两次,然后将其他地方的点相加。但我想知道是否有一种聪明的方法
只需一个查询即可完成所有操作。这就是我现在所拥有的(我运行了两次,第一次是主队,然后是客队):
select
team_home,
sum
(
case
when team_home_goals > team_away_goals then 3
when team_home_goals = team_away_goals then 1
when team_home_goals < team_away_goals then 0
end
)
as points
from
games
group by team_home;