2表联合查询时group by 不起作用
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件.
谢谢
[解决办法]
SELECT part_Num,SUM(inCount) FROM (
SELECT * FROM A
UNION ALL
SELECT part_Num,-1*outCount FROM B ) GROUP BY part_Num
