blocks|key|239574|text|使用您的示例，一个简单的连接似乎就足够了，您为什么需要UNION+ALL？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|239575|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|code-block|syntax|javascript|239576|如果要显示总体总数，可以使用WITH+ROLLUP|239577|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|239578|entityMap^0|R|9|0|0|E|B|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

With your example, a simple join seems to be enough, why would you need to <code>UNION ALL</code> ?

<pre><code>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
</code></pre>

If you want to show the overall total, you can use <code>WITH ROLLUP</code>:

<pre><code>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
</code></pre>

blocks|key|105327|text|你应该用内连接。除非某些表中缺少某些in，否则Union+(All)似乎没有必要：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|105328|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,+())|code-block|syntax|javascript|105329|分组集将添加总行。|105330|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You should use Inner Joins. Union (All) does not seem necessary unless some Ids are missing in some tables:

<pre><code>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, ())
</code></pre>

Grouping set adds the total row.

blocks|key|105344|text|这可以是你所要求的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|105345|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|code-block|syntax|javascript|105346|这可能不是一个合适的答案。|105347|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This can be a solution for what you are asking. 

<pre><code>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
</code></pre>

It might not be an appropriate answer.

blocks|key|105369|text|你们是米希姆的概念。UNION+ALL是很少需要和使用的东西。当将类似的结果集粘合在一起时，您可以使用它。这里不是这样的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|105370|当然，为了连接表，应该使用联接。您将得到一个结果行(在您的例子中是ID+)。您可以使用聚合函数，如SUM、MAX、COUNT等来聚合数据。|105371|要写入的查询取决于表A和其他表中是否存在所有in。区别主要是当时使用的连接类型。|105372|ID必须存在于所有表中：|offset|length|style|BOLD|105373|select+id,+sum(a.adata),+sum(b.bdata),+sum(c.cdata)
from+a
join+b+using+(id)
join+c+using+(id)
group+by+id;|code-block|syntax|javascript|105374|ID必须仅位于表a中：|105375|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;|105376|ID不必存在于任何特定的表中：|105377|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;|105378|编辑:我应该补充一下，Server不支持USING子句(这是标准的SQL2003)。您可以用ON子句替换它，只要不需要完全的外部联接，这个子句就很简单，如果没有using子句，这个连接就复杂得多。|105379|并且:通过使用group+by+rollup(id)，而不是只使用group+by+id，在末尾得到一个sum行。|CODE|105380|entityMap^0|0|0|0|0|C|0|0|0|B|0|0|0|F|0|0|0|7|J|X|B|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|17|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|18|8|@$H|19|I|1A|J|K]]|9|@]|A|$]]|$1|L|3|M|5|N|7|1B|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|1C|8|@$H|1D|I|1E|J|K]]|9|@]|A|$]]|$1|S|3|T|5|N|7|1F|8|@]|9|@]|A|$O|P]]|$1|U|3|V|5|6|7|1G|8|@$H|1H|I|1I|J|K]]|9|@]|A|$]]|$1|W|3|X|5|N|7|1J|8|@]|9|@]|A|$O|P]]|$1|Y|3|Z|5|6|7|1K|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1L|8|@$H|1M|I|1N|J|12]|$H|1O|I|1P|J|12]]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|14|$]]

You are miximg concepts. UNION ALL is something rarely needed and used. You use it when glueing similar result sets together. This is not the case here.

In order to join tables, you should use joins of course. You get a result row per group you state with GROUP BY (the ID in your case). You use aggregation functions such as SUM, MAX, COUNT, etc. to aggregate data.

The query to write depends on whether all IDs are to be present in table A and the other tables. The difference is mainly the kind of join used then.

ID must be present in all tables:

<pre><code>select id, sum(a.adata), sum(b.bdata), sum(c.cdata)
from a
join b using (id)
join c using (id)
group by id;
</code></pre>

ID must be pesent in table a only:

<pre><code>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;
</code></pre>

ID doesn't have to exist in any particular table:

<pre><code>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;
</code></pre>

EDIT: I should add that SQL Server doesn't support the USING clause (which is standard SQL2003). You can replace it with an ON clause, which is easy as long as you don't need full outer joins, which are much more complicated without a using clause.

And: You get a sum row at the end by using <code>group by rollup(id)</code> instead of only <code>group by id</code>.

blocks|key|578052|text|以获得所需的输出(使用SUM行)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|578053|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|code-block|syntax|javascript|578054|输出：|578055|ID++Adata+++Bdata+++Cdata
1+++8383++++11251+++2856
2+++2707++++6335++++1364
3+++6181++++9632++++4476
4+++6708++++11355+++11384
SUM+23979+++38573+++20080|578056|SQL：http://sqlfiddle.com/#!3/ba58d/11/0|offset|length|578057|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/ba58d/11/0^0|0|0|0|0|4|Z|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

To get your desired output (with the SUM row)

<pre><code>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
</code></pre>

Output:

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

SQL Fiddle: <a href="http://sqlfiddle.com/#!3/ba58d/11/0" rel="nofollow">http://sqlfiddle.com/#!3/ba58d/11/0</a>

What are alternatives to UNION ALL for joining many tables to see aggregated data from many source systems? I am looking for solution beyond T-SQL.

Suppose we have 3 source tables and we want to join them by id:

TableA

<pre><code>id Adata
1 8383
2 2707
3 6181
4 6708
</code></pre>

TableB

<pre><code>id Bdata
1 2669
1 8582
2 6335
2 7355
3 7355
3 2277
4 2789
4 8566
</code></pre>

TableC

<pre><code>id Adata
1 2856
2 1364
3 4476
4 3311
4 8073
</code></pre>

The desired result table I want to get:

<a href="https://i.stack.imgur.com/hXno7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hXno7.png" alt="enter image description here"></a>

The primitive way I do now to get it (by UNION ALL):

<a href="https://i.stack.imgur.com/IlQ8U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IlQ8U.png" alt="enter image description here"></a>

Reaching desired results by creation of huge table consisting of many tables under each other and many nulls seems to be very frustrating and primitive solution if we have many columns and many tables. I think this is common problem if we want to join different information systems like accounting, payroll, purchase etc. 

The final aim is to give and Excel user aggregated data with a chance to drill down limited to selected table. For example by double clicking in desired results table in the first Bdata 11251 the user is intrested to see only the data that make that figure. But instead the user gets this:

<a href="https://i.stack.imgur.com/salNG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/salNG.png" alt="enter image description here"></a>

Update. I want to preserve a chance for drill down in data. So I want to be able to see all details for each ID if I double click on results table in Excel.

Alternatives to UNION ALL to get aggregate data

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

对于连接多个表以查看来自多个源系统的聚合数据，UNION所有的替代方案是什么？我正在寻找超越T的解决方案.假设我们有3个源表，并希望通过id连接它们：TableAid  Adata1   83832   27073   61814   6708TableBid  Bdata1   26691   85822   633...

问获得聚合数据的UNION ALL的替代方案
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获得聚合数据的UNION ALL的替代方案EN