android - 混合两个试图获取总量的 sqlite 查询

Question

我需要获取团队目标，使用一个查询而不是两个。

如果出现以下情况，我该怎么做:

此查询获取球队的所有主场进球:

SELECT SUM(home_goals) FROM match WHERE home_team_id = ? 

此查询获取球队的所有客场进球:

SELECT SUM(away_goals) FROM match WHERE away_team_id = ?

我想在一次查询中完成团队的总目标

Answer 1

你可以这样做:

SELECT SUM(goals)
FROM (
  SELECT SUM(home_goals) AS goals
  FROM match
  WHERE home_team_id = 1
  UNION ALL
  SELECT SUM(away_goals)
  FROM match
  WHERE away_team_id = 1
  ) a

sqlfiddle demo

或用例:

SELECT SUM(CASE 
      WHEN home_team_id = 1
        THEN home_goals
      WHEN away_team_id = 1
        THEN away_goals
      ELSE 0
      END) AS goals
FROM match

sqlfiddle demo