create proc p(@total decimal(18,2))
as
declare @num decimal(18,2),@num2 decimal(18,2)
declare cur cursor for
select num,num2 from test where num2<num order by [time]
open cur
fetch next from cur into @num,@num2
while @total>0 and @@fetch_status=0
beginif @num-@num2<@total
begin
update test set num2=num
WHERE CURRENT OF cur
set @total=@total-(@num-@num2)
end
else
begin
update test set num2=num2+@total
WHERE CURRENT OF cur
set @total=0
endfetch next from cur into @num,@num2
end
close cur
deallocate cur
go
order by [time]========>
改为:
order by [time] desc
select 2,200,'2005-02-04',0 union all
select 3,100,'2005-02-05',0select * from @t
/*
(所影响的行数为 3 行)id num time num2
----------- ----------- ------------------------------------------------------ -----------
1 300 2005-02-03 00:00:00.000 0
2 200 2005-02-04 00:00:00.000 0
3 100 2005-02-05 00:00:00.000 0
*/declare @num int
select @num = 500
declare @id int
declare @num_temp int
DECLARE temp_test CURSOR FOR SELECT id, num FROM @t ORDER BY time desc
OPEN temp_testFETCH NEXT FROM temp_test INTO @id, @num_tempWHILE @@FETCH_STATUS = 0
BEGIN
if (@num_temp <= @num)
begin
update @t set num2 = @num_temp where id = @id
set @num = @num - @num_temp
end
if (@num_temp > @num)
begin
update @t set num2 = @num where id = @id
BREAK
end
FETCH NEXT FROM temp_test INTO @id, @num_temp
end
CLOSE temp_test
DEALLOCATE temp_testselect * from @t/*id num time num2
----------- ----------- ------------------------------------------------------ -----------
1 300 2005-02-03 00:00:00.000 200
2 200 2005-02-04 00:00:00.000 200
3 100 2005-02-05 00:00:00.000 100(所影响的行数为 3 行)*/
update test set num2=col2 from
(
select id,case when col1<500 then num else 500-col1+num end as col2 from (
select id,num,(select sum(num)from test a where a.time>=test.time) as col1 from test)a
where case when col1<500 then num else 500-col1+num end >0
) a
where test.id=a.id
declare @t table( id int,num int,[time] datetime,num2 int)
insert into @t
select 1,300,'2005-02-03',0 union
select 2,200,'2005-02-04',0 union
select 3,100,'2005-02-05',0
--传入参数
declare @allnum int
select @allnum=500
--此部分作为存储过程内容
select *
from (
select id,num,time,num2=case when (select @allnum-sum(num) from @t where id>=a.id)>=0 then num else (select @allnum-sum(num) from @t where id>a.id) end
from (select top 100 percent * from @t order by [time] desc) a
) new
where num2>=0
order by [time]
/*结果
1 300 2005-02-03 00:00:00.000 200
2 200 2005-02-04 00:00:00.000 200
3 100 2005-02-05 00:00:00.000 100*/