使用聚合函数获取数据的替代方案，不需要使用UNION ALL。

Question

有什么替代UNION ALL的方法可以连接多个表以查看来自多个源系统的汇总数据？我正在寻找超出T-SQL之外的解决方案。

假设我们有3个源表，我们想通过id将它们连接起来：

TableA

id  Adata
1   8383
2   2707
3   6181
4   6708

表B

id  Bdata
1   2669
1   8582
2   6335
2   7355
3   7355
3   2277
4   2789
4   8566

表格C

id  Adata
1   2856
2   1364
3   4476
4   3311
4   8073

我想要得到的结果表格：

我现在使用联合操作来获取这个结果（通过 UNION ALL）：

如果我们有很多列和很多表，那么创建一个由许多表组成的巨大表格，其中许多表被放置在彼此下方并包含许多空值，似乎是非常令人沮丧和原始的解决方案。我认为这是一个常见的问题，如果我们想要连接不同的信息系统，如会计、工资单、采购等。

最终目的是向Excel用户提供聚合数据，并有机会钻取限于选择的表中。例如，通过双击第一个Bdata 11251中的期望结果表格，用户将只看到生成该数字的数据。但实际上，用户得到的是这样一个结果：

更新。 我想保留数据钻取的机会。因此，如果我在Excel中双击结果表格，我希望能够看到每个ID的所有详细信息。

- Przemyslaw Remin

---

当你拥有一个数据库系统时，使用Excel似乎很奇怪。只需使用某种编程语言（C＃，Java等）编写一个小型GUI程序即可实现此目的。 - Thorsten Kettner

@prz 我已经按照你的要求在我的答案中得到了你所期望的输出，但是我建议在输出结果中添加一个总和行（例如在Excel、SSRS等工具中）。 - Matt

Answer 1

根据您的示例，简单的联接似乎就足够了，为什么需要使用UNION ALL？

SELECT A.id,
    SUM(A.Adata),
    SUM(B.Bdata),
    SUM(C.Cdata)
FROM TableA A
    JOIN TableB B ON A.id = B.id
    JOIN TableC C ON A.id = C.id
GROUP BY A.id

如果您想显示总体合计，可以使用 WITH ROLLUP:

SELECT ISNULL(sub.id,'Total') AS "id",
    sub.Adata,
    sub.Bdata,
    sub.Cdata
FROM (
    SELECT A.id,
        SUM(A.Adata) AS Adata,
        SUM(B.Bdata) AS Bdata,
        SUM(C.Cdata) AS Cdata
    FROM TableA A
        JOIN TableB B ON A.id = B.id
        JOIN TableC C ON A.id = C.id
    GROUP BY A.id WITH ROLLUP) sub

- A Hocevar

---

我使用 UNION ALL 来为 Excel 用户提供在数据透视表中钻取的机会。您的解决方案似乎没有为用户提供展开（显示单个 ID 数据）的机会。 - Przemyslaw Remin

你会允许用户进行哪种钻取操作？你的意思是根本不对数据求和吗？ - A Hocevar

Answer 2

您混淆了概念。UNION ALL 是很少需要和使用的东西。您在将类似的结果集粘合在一起时使用它。但这里不是这种情况。

为了连接表，您应该当然使用联接。您可以通过 GROUP BY（在您的情况下为 ID）指定每个组的结果行。您可以使用聚合函数如 SUM、MAX、COUNT 等来聚合数据。

要编写的查询取决于是否需要在表 A 和其他表中都存在所有 ID。区别主要在于所使用的联接类型。

ID 必须出现在所有表中：

select id, sum(a.adata), sum(b.bdata), sum(c.cdata)
from a
join b using (id)
join c using (id)
group by id;

ID必须只存在于表a中：

select id, sum(a.adata), coalesce(sum(b.bdata),0), coalesce(sum(c.cdata),0)
from a
left join b using (id)
left join c using (id)
group by id;

ID不必存在于任何特定的表中：

select id, coalesce(sum(a.adata),0), coalesce(sum(b.bdata),0), coalesce(sum(c.cdata),0)
from a
full outer join b using (id)
full outer join c using (id)
group by id;

编辑：我应该补充说明SQL Server不支持USING子句（这是标准SQL2003）。您可以使用ON子句替换它，只要您不需要完全外连接，否则没有using子句将变得更加复杂。

还有：您可以通过使用group by rollup(id)而不是仅group by id来在末尾获得一个总和行。

- Thorsten Kettner

Answer 3

您应该使用内连接。Union(All)似乎是不必要的，除非某些表中缺少Ids：

Select coalesce(cast(i.id as varchar(10)), 'SUM')
    , isnull(sum(A.data), '')
    , isnull(sum(B.data), '')
    , isnull(sum(C.data), '')
From (Select id From A
    Union Select id From B
    Union Select id From C
) as i
left Join A on A.id = i.id
left Join B on B.id = i.id
left Join C on C.id = i.id
group by grouping sets (i.id, ())

分组集合添加总行。

- Julien Vavasseur

Answer 4

这可以是你所问的问题的解决方案。

select id,Adata,Bdata,SUM(Cdata) as Cdata 
from ((select id,Adata,SUM(Bdata) as Bdata 
   from (A join B using(id)) group by id)T join C using(id)) 
group by id

这可能不是一个恰当的答案。

- Sparrow7000

---

我认为它不允许钻取。 - Przemyslaw Remin

Answer 5

要获得您想要的输出（包括求和行）

SELECT CAST(A.id AS VARCHAR(100)) AS ID,
(SELECT SUM(Aa.Adata) FROM TableA Aa WHERE A.id = Aa.id GROUP BY Aa.id) AS Adata,
(SELECT SUM(Ba.Bdata) FROM TableB Ba WHERE B.id = Ba.id GROUP BY Ba.id) AS Bdata,
(SELECT SUM(Ca.Cdata) FROM TableC Ca WHERE C.id = Ca.id GROUP BY Ca.id) AS Cdata
FROM TableA A
INNER JOIN TableB B ON A.id = B.id
INNER JOIN TableC C ON A.id = C.id
GROUP BY A.id, B.id, C.id
UNION ALL
SELECT CAST('SUM' AS VARCHAR(100)),
SUM(A.Adata), (SELECT 
SUM(B.Bdata)
FROM TableB B), (SELECT 
SUM(C.Cdata)
FROM TableC C)
FROM TableA A

输出：

ID  Adata   Bdata   Cdata
1   8383    11251   2856
2   2707    6335    1364
3   6181    9632    4476
4   6708    11355   11384
SUM 23979   38573   20080

SQL Fiddle: http://sqlfiddle.com/#!3/ba58d/11/0

SQL Fiddle是一个在线的SQL编辑器和调试工具，用户可以在其中输入SQL代码并进行测试。该网站支持多种数据库系统，包括MySQL、PostgreSQL、Oracle等。用户可以选择自己喜欢的数据库系统，并在其中创建表格、插入数据、执行查询等操作。此外，SQL Fiddle还提供了一些示例代码，供用户参考和学习。

- Matt