表1：instock

 

表2：outorder

将出货数量按入库先后进行分摊。

sql：

select mano,mat,inQty,outQty,
case when outQty - ifnull(qty,0) < 0 then 0 
when outQty - ifnull(qty,0) > inQty then inQty
else outQty - ifnull(qty,0) end outedQty from
(select mano,mat,inQty,outQty,sum(inedQty) qty from
(select t1.*,t2.inQty inedQty from 
(select instock.*,outorder.outqty from instock left join outorder on instock.mat = outorder.mat) t1
left join (select instock.*,outorder.outqty from instock left join outorder on instock.mat = outorder.mat) t2 
on t1.mat = t2.mat and t1.mano > t2.mano) t3 group by mano,mat,inQty,outQty) t

结果：