求一个select语句？

Question

有库存表(t_stock)数据商品ID         批次         数量
(goodsid)   (batchno)      (total)
001           1              20
001           2              10
001           3              30如果我要进行出库操作,比如出库40个,需要按照批次从小到大出库做计划
即第一批次不够,则扣完第一批次,扣二批次,依此类推,请写出SELECT语句
得出下面的结果:商品ID      批次       数量     出库40后数量
(goodsid)  (batchno)  (total)       (total2)
001          1          20           0
001          2          10           0
001          3          30           20

Answer 1

--原始数据：@t_stock
declare @t_stock table(goodsid varchar(3),batchno int,total int)
insert @t_stock
select '001',1,20 union all
select '001',2,10 union all
select '001',3,30 union all
select '001',4,50declare @out int --定义个变量方便测试set @out=40 --这里假设库存足够
select a.*,total2=
(
select
case
when sum(total)<=@out then 0
when a.total>(sum(total)-@out) then sum(total)-@out
else a.total
end
from @t_stock where goodsid=a.goodsid and batchno<=a.batchno
)
from @t_stock a/*
goodsid batchno     total       total2      
------- ----------- ----------- ----------- 
001     1           20          0
001     2           10          0
001     3           30          20
001     4           50          50
*/set @out=120 --考虑库存不够的情况
select a.*,total2=
isnull
(
b.total2,
(
select
case
when sum(total)<=@out then 0
when a.total>(sum(total)-@out) then sum(total)-@out
else a.total
end
from @t_stock where goodsid=a.goodsid and batchno<=a.batchno
)
)
from @t_stock a
left join
(select goodsid,batchno=max(batchno),total2=sum(total)-@out from @t_stock group by goodsid having(sum(total))<@out) b
on a.goodsid=b.goodsid and a.batchno=b.batchno/*
goodsid batchno     total       total2      
------- ----------- ----------- ----------- 
001     1           20          0
001     2           10          0
001     3           30          0
001     4           50          -10
*/

Answer 2

select *,if((a.total+b.total)>=40,(a.total+b.total)%40,40%(a.total+b.total)) as total2
from t_stock as a,t_stock as b
where a.goodsid=b.goodsid and b.batchno=a.batchno;
我的这样,只能完成表面上的事!!