sql - 使用 SQL，将多个表中的列总计为公共(public)组的正确方法是什么？

Question

我正在编写一个仪表板，我需要从 Microsoft SQL Server 中提取一些数据。

举个简单的例子，我有三个表，一个主类别表和两个包含通过主/外键关系链接到类别表的值的表(蓝色和绿色值表)。

使用 Microsoft SQL Sever (t-sql)，我希望对两个值表中的值进行总计(求和)，并按类别表中的常见类别分组。

分类表

CategoryID (PK) | CategoryName
1               | Square
2               | Circle

蓝表

BlueID (PK) | CategoryID (FK) | BlueValue | BlueMonth | BlueYear
1           | 1               | 10        | 6         | 2012
2           | 1               | 20        | 12        | 2012
3           | 2               | 5         | 6         | 2012
4           | 2               | 9         | 12        | 2012
5           | 1               | 12        | 6         | 2013
6           | 1               | 21        | 12        | 2013
7           | 2               | 4         | 6         | 2013
8           | 2               | 8         | 12        | 2013

绿色表格

GreenID (PK)| CategoryID (FK) | GreenValue| GreenMonth| GreenYear
1           | 1               | 3         | 6         | 2012
2           | 1               | 6         | 12        | 2012
3           | 2               | 2         | 6         | 2012
4           | 2               | 7         | 12        | 2012
5           | 1               | 2         | 6         | 2013
6           | 1               | 5         | 12        | 2013
7           | 2               | 4         | 6         | 2013
8           | 2               | 8         | 12        | 2013

如果我使用以下 SQL，我会得到预期的结果。

SELECT
    [Category].[CategoryName],
    SUM([Green].[GreenValue]) AS [GreenTotal]
FROM
    [Category]
LEFT JOIN
    [Green] ON [Category].[CategoryID] = [Green].[CategoryID]
GROUP BY
    [Category].[CategoryName]

结果:

CategoryName | GreenTotal
Square       | 16
Triangle     | 21

但是，如果我添加 Blue 表，并尝试获取 BlueValue 的总计，我显然不正确的 T-SQL 会给我意想不到的结果。

SELECT
    [Category].[CategoryName],
    SUM([Green].[GreenValue]) AS [GreenTotal],
    SUM([Blue].[BlueValue]) AS [BlueTotal]
FROM
    [Category]
LEFT JOIN
    [Green] ON [Category].[CategoryID] = [Green].[CategoryID]
LEFT JOIN
    [Blue] ON [Category].[CategoryID] = [Blue].[CategoryID]
GROUP BY
    [Category].[CategoryName]

不正确的结果:

CategoryName | GreenTotal | BlueTotal
Square       | 64         | 252
Triangle     | 84         | 104

结果似乎都是 4 倍，这是每个类别的每个值表中的总行数。

我的目标是看到以下结果:

CategoryName | GreenTotal | BlueTotal
Square       | 16         | 63
Triangle     | 21         | 26

如果有人能告诉我到底哪里做错了，我会欣喜若狂吗？

谢谢，马克。

Answer 1

在我看来，像这样的事情最好用 APPLY 来完成。在查询发生变化的情况下，性能快速、使用简单且易于控制。

即:

SELECT C.[CategoryName], G.[GreenTotal], B.[BlueTotal]
FROM [Category] C
OUTER APPLY (SELECT SUM([GreenValue]) AS [GreenTotal] FROM [Green] WHERE [CategoryID] = C.CategoryID) G
OUTER APPLY (SELECT SUM([BlueValue]) AS [BlueTotal] FROM [Blue] WHERE [CategoryID] = C.CategoryID) B