多表合计查询

Question

1、产品表，Goods，字段ID/Name。
2、入货表，DetailIn，字段GoodsId/Quantity。
3、出库表，DetailOut，字段GoodsId/Quantity。
问题：如何在一个SQL中实现指定产品的入库合计、出库合计、余货计算。我是这样写的
Select Goods.Id, Goods.Name, ISNULL(SUM(DetailIn.Quantity),0) As dInQty, ISNULL(SUM(DetailOut.Quantity),0) As dOutQty, ISNULL(SUM(DetailIn.Quantity),0)-ISNULL(SUM(DetailOut.Quantity),0) As dLeftQty From Goods Left Join DetailIn On Goods.Id=DetailIn.GoodsId Left Join DetailOut On Goods.Id=DetailOut.GoodsId Where Goods.Id='A001' Group By Goods.Id, Goods.Name这样写有问题，如果入库有3次、出库有2次，那么各个合计就会都有重复的情况出现。
请教各位如何处理这个问题，感谢！

Answer 1

思路一般是两个left join，不过最好你给出一点测试数据和期待结果

Answer 2

WITH I AS
(
SELECT GoodsId,SUM(Quantity) Quantity
FROM DetailIn
), O AS
(
SELECT GoodsId,SUM(Quantity) Quantity
FROM DetailOut
)SELECT G.*,ISNULL(I.Quantity,0) dInQty,ISNULL(O.Quantity,0) dOutQty,ISNULL(I.Quantity,0)-ISNULL(O.Quantity,0) dLeftQty
FROM Goods G LEFT JOIN I ON G.ID=I.GoodsId
LEFT JOIN O ON G.ID=O.GoodsId

Answer 3

;WITH I AS
(
SELECT GoodsId,SUM(Quantity) Quantity
FROM DetailIn
GROUP BY GoodsId
), O AS
(
SELECT GoodsId,SUM(Quantity) Quantity
FROM DetailOut
GROUP BY GoodsId
)SELECT G.*,ISNULL(I.Quantity,0) dInQty,ISNULL(O.Quantity,0) dOutQty,ISNULL(I.Quantity,0)-ISNULL(O.Quantity,0) dLeftQty
FROM Goods G LEFT JOIN I ON G.ID=I.GoodsId
LEFT JOIN O ON G.ID=O.GoodsId

Answer 4

补充数据：1、产品表，Goods，字段ID/Name。A001/B2铅笔2、入货表，DetailIn，字段ID/GoodsId/Quantity。1/A001/100
2/A001/200
3/A001/5003、出库表，DetailOut，字段ID/GoodsId/Quantity。1/A001/400
2/A001/80用上述Left Join查询出来的结果：
Id/Name/dInQty/dOutQty/dLeftQty
A001/B2铅笔/1600/1440/160结果可以看出，入库合计变成了入库合计*出库次数，出库合计变成了出库合计*入库次数。我想问有没有办法一个SQL里实现各算各的。

Answer 5

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-30 10:54:42
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
-- Jun 10 2013 20:09:10 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[Goods]
if object_id('[Goods]') is not null drop table [Goods]
go 
create table [Goods]([字段ID] varchar(4),[Name] varchar(6))
insert [Goods]
select 'A001','B2铅笔'
--> 测试数据：[DetailIn]
if object_id('[DetailIn]') is not null drop table [DetailIn]
go 
create table [DetailIn]([字段ID] int,[GoodsId] varchar(4),[Quantity] int)
insert [DetailIn]
select 1,'A001',100 union all
select 2,'A001',200 union all
select 3,'A001',500
--> 测试数据：[DetailOut]
if object_id('[DetailOut]') is not null drop table [DetailOut]
go 
create table [DetailOut]([字段ID] int,[GoodsId] varchar(4),[Quantity] int)
insert [DetailOut]
select 1,'A001',400 union all
select 2,'A001',80
--------------开始查询--------------------------select a.*,[DetailIn].[Quantity],[DetailOut].[Quantity]
from [Goods] a LEFT JOIN (SELECT goodsid,SUM([Quantity])[Quantity] FROM [DetailIn] GROUP BY goodsid)[DetailIn] ON a.[字段ID]=[DetailIn].goodsid
LEFT JOIN (SELECT goodsid,SUM([Quantity])[Quantity] FROM [DetailOut] GROUP BY goodsid)[DetailOut] ON a.[字段ID]=[DetailOut].goodsid
----------------结果----------------------------
/* 
字段ID Name   Quantity    Quantity
---- ------ ----------- -----------
A001 B2铅笔   800         480
*/