mysql - 如何区分两个求和查询

Question

我必须计算足球队的净胜球。基本上是:(goalsScoredAtHome + goalsScoredAway) - (goalsConciededAtHome + goalsConciededAway)一切都保存在一张表中:

homeTeam | awayTeam | goalsHome | goalsAway
  USA    |  Poland  |     2     |    0
 Poland  |   USA    |     3     |    1

这是我的 4 个独立查询:

(select sum(goalsHome) as GoalsScoredHome from game where home = 'USA' 
+
select sum(goalsAway) as GoalsScoredAway from game where away = 'USA')
-
(select sum(goalsAway) as GoalsConciededHome from game where home = 'USA'
+
select sum(goalsHome) as GoalsConciededAway from game where away = 'USA')

有什么方法可以在一次查询中完成吗？

Answer 1

您的查询的直接翻译使用条件聚合:

select (sum(case when home = 'USA' then goalsHome else 0 end) +
        sum(case when away = 'USA' then goalsAway else 0 end)
       ) -
       (sum(case when home = 'USA' then goalsAway else 0 end) +
        sum(case when away = 'USA' then goalsHome else 0 end)
       )
from game;

您可以将其简化为:

select (sum(case when home = 'USA' then goalsHome - goalsAway else 0 end) +
        sum(case when away = 'USA' then goalsAway - goalsHome else 0 end)
       )
from game;