请教个sql!

Question

库存
id name qty lot 
1   a   200  a1
2   a   400  b1
3   a   100  c1结果为 id name qty  lot 
1   a  50   b1
2   a  100  c1
要领550数量的 a产品,不用游标，怎么实现？

Answer 1

create table tb(id int,name varchar(10),qty int,lot varchar(10))
insert into tb values(1 ,'a', 200 ,'a1')
insert into tb values(2 ,'a', 400 ,'b1')
insert into tb values(3 ,'a', 100 ,'c1')
godeclare @num as int
set @num = 550select id , name , qty = qty1 , lot from
(
select t.* , qty1 = (
       case when (select sum(qty) from tb where name = 'a' and id <= t.id) <= @num then 0 
            when (select sum(qty) from tb where name = 'a' and id <= t.id) - @num <= qty then (select sum(qty) from tb where name = 'a' and id <= t.id) - @num
            when (select sum(qty) from tb where name = 'a' and id <= t.id) + qty > @num then qty 
       end)
from tb t where name = 'a'
) m where qty1 > 0drop table tb/*
id          name       qty         lot        
----------- ---------- ----------- ---------- 
2           a          50          b1
3           a          100         c1（所影响的行数为 2 行）
*/

Answer 2

create table tb(id int,name varchar(10),qty int,lot varchar(10))
insert into tb select 1,'a',200,'a1'
insert into tb select 2,'a',400,'b1'
insert into tb select 3,'a',100,'c1'
go
--设id连续
declare @q int
set @q=550
;with cte as(
select top 1 id,name,qty-@q as qty,@q-qty as leftq,lot from tb order by id
union all
select b.id,b.name,b.qty-a.leftq,a.leftq-b.qty as leftq,b.lot 
from cte a inner join tb b on a.name=b.name and a.id=b.id-1 where a.leftq>0
)
select id-(select max(id) from cte where qty<=0)id,name,qty,lot from(
select id,name,qty,lot from cte where qty>0
union all
select * from tb where id>(select max(id) from cte)
)t
go
drop table tb
/*
id          name       qty         lot
----------- ---------- ----------- ----------
1           a          50          b1
2           a          100         c1(2 行受影响)
*/