日期:2014-05-16  浏览次数:20864 次

2表联合查询的合计问题
a是入库表 
b是出库表 

c是产品基本表 

某产品x入库一次10件,出库2次每次10件共20件 

想用一条语句查询出入库合计和出库合计,用以下语句,但有错误: 

select 
  c.Part_Num, 
  sum(a.InCount) as inCount, 
  sum(b.OutCount) as outCount 
from c 
left join a on c.Part_Num=a.Part_Num 
left join b on b.Part_Num=a.Part_Num 
group by c.Part_Num 

正确结果入库合计10件,出库合计20件 

但用以上语句显示结果为入库合计20件,出库合计20件. 

a表 
part_Num inCount 
x 10 

b表 
part_Num outCount 
x 10 
x 10 

想要的结果是在一条记录中得到入库合计和出库合计。 

part_Num sum(inCount) sum(outCount) 
x 10 20

谢谢

------解决方案--------------------
SQL code
select
  c.Part_Num,
  a.InCount as inCount,
  b.OutCount as outCount
from c
left join ((select part_Num,sum(inCount) as inCount from a group by part_Num)) as a on c.Part_Num=a.Part_Num
left join (select part_Num,sum(outCount) as outCount from b group by part_Num) as b on b.Part_Num=a.Part_Num
group by c.Part_Num

------解决方案--------------------
SQL code
select Part_Num,sum(InCount),sum(OutCount)
from (
select Part_Num,InCount,0 as OutCount from a
union all
select Part_Num,0 as InCount,OutCount from b
union all
select Part_Num,0,0 from c
)
group by Part_Num