SQL查询以汇总不同表中的字段 [英] SQL Query to sum fields from different tables

问题描述

我是一个讨厌SQL的谦虚程序员... :)请帮助我进行此查询.

I'm a humble programmer that hates SQL ... :) Please help me with this query.

我有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

我需要做一个SELECT来显示表B，C和D的ID，总计和SUM的Amount字段

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.

推荐答案

或者您可以利用使用子查询的优势:

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查询以汇总不同表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！