用于计算游戏点数的sql [英] sql for calculating points for games

问题描述

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

I have a table with football matches.

桌上游戏:

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);

我需要创建一个查询以获取具有位置的表. 根据表格上方的插入内容，将为:

I need to create a query to get the table with positions. According to the inserts above the table would be:

1 - City 6 points  
2 - ManUTD 3 points
3 - Tottenham 3 points
4 - Chelsea 2 points
5 - Liverpool 1 point
6 - Arsenal 1 point

为此创建的查询是针对主队"或客队"的. 我可以运行两次此查询，然后在其他地方求和.但我想知道是否有一种聪明的方法来 在一个查询中完成所有操作这就是我现在所拥有的(我两次运行本垒打，首先是主队，然后是客队):

The query I create for this is either for "home teams" or "away teams". I could run this query twice and then sum the points some where else. But I wonder if there is a smart way to do everything in only one query. This is what I have now (I run this twice, first for home team and then for away team):

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;

推荐答案

您可以使用UNION:

SELECT team, SUM(points) AS total_points
FROM (
  SELECT team_home AS team, 
         CASE 
            WHEN team_home_goals > team_away_goals THEN 3
            WHEN team_home_goals = team_away_goals THEN 1
            ELSE 0
         END AS points
  FROM games

  UNION ALL

  SELECT team_away AS team, 
         CASE 
            WHEN team_away_goals > team_home_goals THEN 3
            WHEN team_away_goals = team_home_goals THEN 1
            ELSE 0
         END AS points
  FROM games ) AS t
GROUP BY team
ORDER BY total_points DESC

此处演示

这篇关于用于计算游戏点数的sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！