SQLite和和

Question

假设我有两张桌子看起来是这样的：

Games:
| AwayTeam  | HomeTeam     | AwayPoints | HomePoints |
------------------------------------------------------
| Aardvarks | Bobcats      | 2          | 1          |
| Bobcats   | Caterpillars | 20         | 10         |
| Aardvarks | Caterpillars | 200        | 100        |

Teams:
| Name         | 
----------------
| Aardvarks    | 
| Bobcats      |
| Caterpillars |

我怎么能做出这样的结果呢？

| Name         | TotalPoints |
------------------------------
| Aardvarks    | 202         |
| Bobcats      |  21         | 
| Caterpillars | 110         |

我认为我真正的问题是如何在SQL中将语句拼接在一起。这两项声明各自运作良好：

SELECT SUM ( AwayPoints ) 
FROM   Games 
WHERE  AwayTeam='Bobcats';

SELECT SUM ( HomePoints ) 
FROM   Games 
WHERE  HomeTeam='Bobcats';

如果我想将两个复合算子语句拼接在一起，我怀疑我需要一个选择。然后将该语句传递到下面的聚合表达式中：

SELECT   Name, SUM( aggregate_expression ) 
AS       'TotalPoints' 
FROM     Teams 
GROUP BY Name;

如果我必须把这一切都放在一起，我想我最终会得到这样的结果：

SELECT   Name, SUM (
    SELECT SUM ( AwayPoints ) 
    FROM   Games 
    WHERE  AwayTeam=Name 
  UNION 
    SELECT SUM ( HomePoints )
    FROM   Games 
    WHERE  HomeTeam=Name
)
AS       'TotalPoints' 
FROM     Teams 
GROUP BY Name;

但是，这不起作用，因为SELECT SUM ( SELECT ...完全无效

Answer 1

使用UNION ALL

SELECT team, SUM(points)
FROM (
  SELECT HomeTeam AS team, SUM(HomePoints) AS points
  FROM Games
  GROUP BY HomeTeam
 UNION ALL
  SELECT AwayTeam AS team, SUM(AwayPoints) AS points
  FROM Games
  GROUP BY AwayTeam
)
GROUP BY team

请参阅SQLite文档

• 选择 费用作为别名
• 选择。从… (选择-stmt)作为表别名