积分mysql_mysql – 用于计算游戏积分的sql

我有一张足球比赛的桌子.

桌上游戏：

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;