代码之家 › 专栏 › 技术社区 › Stewart

SQLite总和

sqlite3 sqlite

1

Stewart · 技术社区 · 7 年前

假设我有两张这样的桌子:

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         |

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

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

我想我需要一个 compound operator 如果我要拼接两个 SELECT 一起发言。然后将该语句传递到下面的聚合表达式中:

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 ...

1 回复 | 直到 7 年前

1

Stewart 7 年前

使用 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