CREATE TABLE test (a CHAR(10), b INT)
INSERT INTO test
SELECT 'aaa',20
INSERT INTO test
SELECT 'bbb',20目前遇到一个问题,想减去一定的金额,按照一定的顺序,走最后一个开始减去,例如,100块,先减去aaa的,然后剩下的80减去bbb的,试着用while语句来做,但是不知道怎么写判断语句让其减到0自动退出。这里请教下高手,谢谢!
INSERT INTO test
SELECT 'aaa',20
INSERT INTO test
SELECT 'bbb',20目前遇到一个问题,想减去一定的金额,按照一定的顺序,走最后一个开始减去,例如,100块,先减去aaa的,然后剩下的80减去bbb的,试着用while语句来做,但是不知道怎么写判断语句让其减到0自动退出。这里请教下高手,谢谢!
begin
执行代码
if 最后的值<=0 breakend
create table test (a varchar(10), b int);
insert into test
select 'aaa',20 union all select 'bbb',20 union all
select 'ccc',40 union all select 'ddd',40;-- 是这个意思?
declare @sum int;
set @sum=100;
declare cur cursor for select * from test order by a;
open cur;declare @a varchar(10), @b int;
while @@fetch_status=0 and @sum>0
begin
fetch next from cur into @a,@b
set @sum=@sum-@b
select @sum
endclose cur;
deallocate cur;
fetch next from cur into @a,@b
IF @b<=@sum
BEGIN
UPDATE test SET b=0 WHERE a=@a
END
ELSE
UPDATE test SET b=@b-@sum WHERE a=@a
set @sum=@sum-@b
SELECT @sum