select a.Factory_ID [厂商代号],c.产品规格,c.订货总量,d.入库数,c.订货总量-d.入库数 [ 未入库数量],e.退货数,case when c.订货总量-d.入库数=0 then '完毕' else '未完毕' end [收货完毕] from Factory a
join Bill b on a.Factory_ID=b.Factory_ID
join Bill_Product c on b.Bill_ID=c.Bill_ID
join (select Product_ID,sum([count]) 入库数 from Product_IN group by Product_ID) d
on c.Product_ID=d.Product_ID
join (select Product_ID,sum([count]) 退货数 from [Return] group by Product_ID) e
on c.Product_ID=e.Product_ID
join Bill b on a.Factory_ID=b.Factory_ID
join Bill_Product c on b.Bill_ID=c.Bill_ID
join (select Product_ID,sum([count]) 入库数 from Product_IN group by Product_ID) d
on c.Product_ID=d.Product_ID
join (select Product_ID,sum([count]) 退货数 from [Return] group by Product_ID) e
on c.Product_ID=e.Product_ID
同一订单号下,产品ID应以序号表示;
在入库时,应以‘订单号’+‘序号’来确定入库那一张定订单的那一项。
出库时亦同,
你用‘产品的自动增加编号’能解决问题吗?