一句UPDATE语句 解决 多条入库批次 库存依次出库

问题就是按库存依次出库

表A中有一字段 Amount
数据举例如下：
ID , Amount
1    2
2    1
3    4


现要实现的功能如下
按照上面的顺序 ， 要减去一个 Amount 总数（比如 5）
则 更新后的数据如下
ID , Amount
1    0        //减掉了 5 中的 2
2    0        //减掉了 5 中的 1
3    2        //减掉了 5 中的 2

 

 

 

 create table test(id int,amount int);
insert into test values(1,2);
insert into test values(2,1);
insert into test values(3,4);
insert into test values(4,5);

update test p
   set p.amount = (select q.amount
                     from (select id,
                                  case when amount1 < amount then amount1 else amount end as amount
                             from (select id,amount,case when t < 5 then 0 else t - 5 end as amount1
                                     from (select id, amount,amount + nvl((select sum(amount)
                                                                             from test
                                                                            where id < a.id
                                                                           ),0) as t
                                             from test a
                                           )
                                  )
                           ) q
                    where q.id = p.id)

select * from test

drop table test