27,582
社区成员




create table tb(id int,amount int)
insert tb select 1,100
insert tb select 2,80
insert tb select 3,72
insert tb select 4,51
declare @i int
set @i=0
update tb set amount=@i-200,@i=@i+amount
delete tb where amount<0
declare @tb table (id int,am int)
insert into @tb select 1,100
insert into @tb select 2,80
insert into @tb select 3,72
insert into @tb select 4,51
declare @i int,@sum int
select @i=count(1) from @tb
select @sum=max(su) from (
select * ,su=(select sum(am) su from @tb where id<=a.id ) from @tb a )a
while @sum>200
begin
delete @tb where id=(select min(id) from @tb)
select @sum=max(su) from (
select * ,su=(select sum(am) su from @tb where id<=a.id ) from @tb a )a
end
select * from @tb
create table tb(id int,amount int)
insert tb select 1,100
insert tb select 2,80
insert tb select 3,72
insert tb select 4,51
declare @i int
set @i=0
update tb set @i=@i+amount,
amount=@i
delete tb where amount<200
select * from tb
drop table tb
/*
id amount
----------- -----------
3 252
4 303
(所影响的行数为 2 行)
*/