declare @aa int set @aa=1000 update table1 set cc = case when @aa > bb then bb else @aa end if @aa>bb update table1 set bb=@aa-bb update table1 set @aa = case when @aa > dd then @aa-bb else 0 end
drop table tab create table tab (借款编号 int, 金额 int ,日期 char(10),temp int)insert into tab select 1,100,'2005-01-01',0 insert into tab select 2, 50 , '2005-03-02',0 insert into tab select 3, 200 ,'2005-09-01' ,0 insert into tab select 4, 500 , '2005-10-01',0 begin tran declare @aa int declare @bb int set @aa=400 update tab set @bb=@aa ,@aa=case when 金额<@bb then @bb-金额 else 金额-@bb end ,金额=case when 金额<@bb then 0 else 金额-@bb end select * from tab rollback tran
declare @tb table ( [借款编号] int, [金额] int , [日期] char(10) )insert into @tb select 1,100,'2005-01-01' union select 2,50 , '2005-03-02' union select 3,200 ,'2005-09-01' union select 4,500, '2005-10-01'--更新 update @tb set [金额]=(case when (select sum([金额]) from @tb where [借款编号]<=t.[借款编号])<=400 then 0 else (select sum([金额]) from @tb where [借款编号]<=t.[借款编号])-400 end ) from @tb t--查看 select * from @tb--结果 /* 借款编号 金额 日期 ----------- ----------- ---------- 1 0 2005-01-01 2 0 2005-03-02 3 0 2005-09-01 4 450 2005-10-01(4 row(s) affected) */
--基本上是支持小小的,但是如果再插入一笔记录到tab中,就出问题了 --要修改一下 update tab set @bb=@aa ,@aa=case when 金额<@bb then @bb-金额 else 0 end /** 金额-@bb 改为0 **/ ,金额=case when 金额<@bb then 0 else 金额-@bb end
set @aa = 100
table1
bb
1
2
3
4
5update table1 set
bb = @aa - bb
@aa = @aa -bb
结果应该是
99
97
94
...
set @aa=1000
update table1 set
cc = case when @aa > bb then bb
else @aa
end
if @aa>bb
update table1 set
bb=@aa-bb
update table1 set
@aa = case when @aa > dd then @aa-bb
else 0
end
create table tab (借款编号 int, 金额 int ,日期 char(10),temp int)insert into tab select 1,100,'2005-01-01',0
insert into tab select 2, 50 , '2005-03-02',0
insert into tab select 3, 200 ,'2005-09-01' ,0
insert into tab select 4, 500 , '2005-10-01',0
begin tran
declare @aa int
declare @bb int
set @aa=400
update tab set @bb=@aa
,@aa=case when 金额<@bb then @bb-金额 else 金额-@bb end
,金额=case when 金额<@bb then 0 else 金额-@bb end
select * from tab
rollback tran
(
[借款编号] int,
[金额] int ,
[日期] char(10)
)insert into @tb
select 1,100,'2005-01-01' union
select 2,50 , '2005-03-02' union
select 3,200 ,'2005-09-01' union
select 4,500, '2005-10-01'--更新
update @tb
set [金额]=(case when (select sum([金额]) from @tb where [借款编号]<=t.[借款编号])<=400
then 0
else (select sum([金额]) from @tb where [借款编号]<=t.[借款编号])-400
end
)
from @tb t--查看
select * from @tb--结果
/*
借款编号 金额 日期
----------- ----------- ----------
1 0 2005-01-01
2 0 2005-03-02
3 0 2005-09-01
4 450 2005-10-01(4 row(s) affected)
*/
--要修改一下
update tab set @bb=@aa
,@aa=case when 金额<@bb then @bb-金额 else 0 end /** 金额-@bb 改为0 **/
,金额=case when 金额<@bb then 0 else 金额-@bb end
v1 v2
1 0
2 0
3 0
set @w = 4
表的结果应该是如下,sum(v2) = 4
v1 v2
1 1
2 1
3 2