SQL SQL查询汇总不同表中的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2301647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query to sum fields from different tables
提问by Ignacio Soler Garcia
I'm a humble programmer that hates SQL ... :) Please help me with this query.
我是一个讨厌 SQL 的谦虚程序员...... :) 请帮我解决这个查询。
I have 4 tables, for example:
我有 4 个表，例如：
Table A:
Id Total
1 100
2 200
3 500
Table B
ExtId Amount
1 10
1 20
1 13
2 12
2 43
3 43
3 22
Table C
ExtId Amount
1 10
1 20
1 13
2 12
2 43
3 43
3 22
Table D
ExtId Amount
1 10
1 20
1 13
2 12
2 43
3 43
3 22
I need to make a SELECT that shows the Id, the Total and the SUM of the Amount fields of tables B, C and D like this
我需要做一个 SELECT 来显示表 B、C 和 D 的 Amount 字段的 Id、Total 和 SUM，像这样
Id Total AmountB AmountC AmountD
1 100 43 43 43
2 200 55 55 55
3 500 65 65 65
I've tried with a inner join of the three tables by the Id and doing a sum of the amount fields but results are not rigth. Here is the wrong query:
我已经尝试通过 Id 对三个表进行内部连接并对金额字段进行求和，但结果不正确。这是错误的查询：
SELECT dbo.A.Id, dbo.A.Total, SUM(dbo.B.Amount) AS Expr1, SUM(dbo.C.Amount) AS Expr2, SUM(dbo.D.Amount) AS Expr3
FROM dbo.A INNER JOIN
dbo.B ON dbo.A.Id = dbo.B.ExtId INNER JOIN
dbo.C ON dbo.A.Id = dbo.C.ExtId INNER JOIN
dbo.D ON dbo.A.Id = dbo.D.ExtId
GROUP BY dbo.A.Id, dbo.A.Total
Thanks in advance, its just that I hate SQL (or that SQL hates me).
提前致谢，只是我讨厌 SQL（或者 SQL 讨厌我）。
EDIT: I had a typo. This query is not giving the right results. Extended the example.
编辑：我有一个错字。此查询没有给出正确的结果。扩展了示例。
回答by odiseh
Or you can take advantage of using SubQueries:
或者您可以利用使用子查询：
select A.ID, A.Total, b.SB as AmountB, c.SC as AmountC, d.SD as AmountD
from A
inner join (select ExtID, sum(Amount) as SB from B group by ExtID) b on A.ID = b.ExtID
inner join (select ExtID, sum(Amount) as SC from C group by ExtID) c on c.ExtID = A.ID
inner join (select ExtID, sum(Amount) as SD from D group by ExtID) d on d.ExtID = A.ID
回答by a'r
From your description, this query should give you an error as you are using the non-existent column dbo.A.Amountin your group by. Changing this to dbo.A.Totalmight be what you need.
根据您的描述，当您使用dbo.A.Amountgroup by 中不存在的列时，此查询应该会给您一个错误。将其更改为dbo.A.Total可能正是您所需要的。
If you need all the amounts together, then try this query:
如果您需要将所有金额放在一起，请尝试以下查询：
select A.Id, A.Total, sum(B.Amount + C.Amount + D.Amount) AS Total_Amount
from A
inner join B on A.Id = B.ExtId
inner join C on A.Id = C.ExtId
inner join D on A.Id = D.ExtId
group by A.Id, A.Total;
回答by jahson kimulu
This one also works well
这个也很好用
SELECT (SELECT SUM(Amount) FROM TableA) AS AmountA,
(SELECT SUM(Amount) FROM TableB) AS AmountB,
(SELECT SUM(Amount) FROM TableC) AS AmountC,
(SELECT SUM(Amount) FROM TableD) AS AmountD
SELECT (SELECT SUM(Amount) FROM TableA) AS AmountA,
(SELECT SUM(Amount) FROM TableB) AS AmountB,
(SELECT SUM(Amount) FROM TableC) AS AmountC,
(SELECT SUM(Amount) FROM TableD) AS AmountD
回答by user3127648
This might help other users.
这可能会帮助其他用户。
SELECT Total=(Select Sum(Amount) from table a)+(Select Sum(Amount) from table b)+(Select Sum(Amount) from table c)
回答by mitul
Try this code SELECT Total=isnull((Select Sum(Isnull(Amount,0)) from table a),0)+isnull((Select Sum(isnull(Amount,0)) from table b),0)+isnull((Select Sum(isnull(Amount,0)) from table c),0)
试试这个代码 SELECT Total=isnull((Select Sum(Isnull(Amount,0)) from table a),0)+isnull((Select Sum(isnull(Amount,0)) from table b),0)+isnull( (从表 c),0 中选择 Sum(isnull(Amount,0))
