SQL Query 对不同表中的字段求和 [英] SQL Query to sum fields from different tables
问题描述
我是一个卑微的程序员,讨厌 SQL ... :) 请帮我解决这个问题.
I'm a humble programmer that hates SQL ... :) Please help me with this query.
我有 4 个表,例如:
I have 4 tables, for example:
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
我需要像这样显示表 B、C 和 D 的 Amount 字段的 Id、Total 和 SUM 的 SELECT
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
Id Total AmountB AmountC AmountD
1 100 43 43 43
2 200 55 55 55
3 500 65 65 65
我尝试通过 Id 对三个表进行内部连接,并对金额字段进行求和,但结果不正确.这是错误的查询:
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:
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
提前谢谢,只是我讨厌 SQL(或者 SQL 讨厌我).
Thanks in advance, its just that I hate SQL (or that SQL hates me).
我有一个错字.这个查询没有给出正确的结果.扩展示例.
I had a typo. This query is not giving the right results. Extended the example.
推荐答案
或者你可以利用 SubQueries:
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
这篇关于SQL Query 对不同表中的字段求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!