表test(Id int,value int)
insert into test values(1,12)
insert into test values(2,22)
insert into test values(3,42)
insert into test values(4,52)要实现功能 如有一个数 55 我想用 value-55 每一笔value最小只能为0,结果如
1,0
2,0
3,21
4,52
insert into test values(1,12)
insert into test values(2,22)
insert into test values(3,42)
insert into test values(4,52)要实现功能 如有一个数 55 我想用 value-55 每一笔value最小只能为0,结果如
1,0
2,0
3,21
4,52
create table wujinyuan (Id int,value int)
insert into wujinyuan values(1,12)
insert into wujinyuan values(2,22)
insert into wujinyuan values(3,42)
insert into wujinyuan values(4,52) update wujinyuan set value = case when value-55 > 0 then value-55 else 0 end
表test(Id int,value int)
insert into test values(1,12)
insert into test values(2,22)
insert into test values(3,42)
insert into test values(4,52) sum(value) 总数是为128
要实现功能 如有一个数 cc=55 使结果如下
1,0
2,0
3,21
4,52
insert into test values(1,12)
insert into test values(2,22)
insert into test values(3,42)
insert into test values(4,52) sum(value) 总数是为128
要实现功能 如有一个数 我要用 test里面的value值减cc=55
第一笔 if value>=cc then value = value-cc,cc=0 --将value的值update 到test 表中
if value< cc then value = 0,cc=cc-value --将value的值update 到test 表中
--如果cc大于0 就减下一笔。。一直减到cc为0 注意表test sum(value)一定是大于cc的使结果如下
1,0
2,0
3,21
4,52
insert into test values(1,12)
insert into test values(2,22)
insert into test values(3,42)
insert into test values(4,52) declare @cc int
set @cc = 55
declare @id int
declare @value int
declare cur cursor for select id,value from test
open cur
fetch next from cur into @id,@value
while @@fetch_status = 0
begin
if @value < @cc
begin
update test set value = 0 where id = @id
set @cc = @cc - @value
end
else
begin
update test set value = value - @cc where id = @id
set @cc = 0
end
if @cc = 0 break
fetch next from cur into @id,@value
end
close cur
deallocate cur
select * from test/*
Id value
----------- -----------
1 0
2 0
3 21
4 52(所影响的行数为 4 行)
*/
drop table test