您当前的位置: 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