selet @a = max(id) from pp
begin
执行语句
update pp set num = num -1 where num <> 0
selet @b=num from pp where id=@a
if @b=0
update pp set num=num -1 where id=@a
end
begin
执行语句
update pp set num = num -1 where num <> 0
selet @b=num from pp where id=@a
if @b=0
update pp set num=num -1 where id=@a
end
insert into pp values(1, 3, 2 )
insert into pp values(2, 2, 2 )
insert into pp values(3, 1, 3 )
insert into pp values(4, 1, 5 )
insert into pp values(5, 8, 3 )
go
declare @m as intset @m = 10
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 and id <> (select max(id) from pp) then 0 else (select sum(num) from pp where id <= t.id) - @m end , week from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 5 3(所影响的行数为 5 行)
*/set @m = 20
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 and id <> (select max(id) from pp) then 0 else (select sum(num) from pp where id <= t.id) - @m end , week from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 -5 3(所影响的行数为 5 行)
*/drop table pp
set num = case when @m > num then 0 else - @m end,@m=@m - num
select * from pp
as
begin
declare @sum int;
declare @min int;
----如果减法不能成功,就是你提供的m不能使每一行(除了最后一行)变为你想要得结果。
select @min = min(num) from table
where id not in(select top 1 id from table order by id desc )
if @min>@m
begin
---提示: 不满足条件的@m
RETURN;
end
--求出所有行的num合计
select @sum = sum(num) from table
---更新每一行(除了最后一行)
UPDATE TABLE
SET num=@RESULT
WHERE id not in(select top 1 id from table order by id desc )
---更新最后一行
UPDATE TABLE
SET num=@sum-@M
WHERE id =(select top 1 id from table order by id desc )end
当m=10,执行完后,m=0,其后面的行数会受到影响我需要是当@m-num等于0时,后面的行不再执行
结果是出来了,但是会影响其它行,我希望当@m-num等于0时,就不再执行了。现在是一执行,所有行都会改变数据。谢谢
insert into pp values(1, 3, 2 )
insert into pp values(2, 2, 2 )
insert into pp values(3, 1, 3 )
insert into pp values(4, 1, 5 )
insert into pp values(5, 8, 3 )
insert into pp values(6, 10, 3 )
insert into pp values(7, 10, 3 )
insert into pp values(8, 10, 3 )go
declare @m as intset @m = 10
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 then 0
when (select sum(num) from pp where id <= t.id) - @m between 0 and num then (select sum(num) from pp where id <= t.id) - @m
else num end , week
from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 5 3
6 10 3
7 10 3
8 10 3(所影响的行数为 8 行)
*/set @m = 20
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 then 0
when (select sum(num) from pp where id <= t.id) - @m between 0 and num then (select sum(num) from pp where id <= t.id) - @m
else num end , week
from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 0 3
6 5 3
7 10 3
8 10 3(所影响的行数为 8 行)
*/drop table pp