try:--找出@fAmount为0的临界点
declare @noid int
select @noid=noid from tblA t
where (select sum(fAmountBal) from tblA where id<=t.id)<=@fAmount
and
(select sum(fAmountBal) from tblA where id<=t.id+1)>@fAmount
--更新临界点以前的数据
update tblA
set fAmountBal=(t.fAmount-(@fAmount-(select sum(fAmountBal) from tblA where id<=t.id)))
from tblA t
where id<=@noid--更新临界点以后的数据
update tblA
set fAmountBal=0
from tblA t
where id>@noid
declare @noid int
select @noid=noid from tblA t
where (select sum(fAmountBal) from tblA where id<=t.id)<=@fAmount
and
(select sum(fAmountBal) from tblA where id<=t.id+1)>@fAmount
--更新临界点以前的数据
update tblA
set fAmountBal=(t.fAmount-(@fAmount-(select sum(fAmountBal) from tblA where id<=t.id)))
from tblA t
where id<=@noid--更新临界点以后的数据
update tblA
set fAmountBal=0
from tblA t
where id>@noid
a
set
@fAmount = @fAmount - a.fAmountBal,
fAmountBal = (case when a.fAmountBal > @fAmount then a.fAmount-@fAmount else 0 end)
from
tblA a
insert into #tblA select 1,200 ,200
insert into #tblA select 2,100.2,100.2
insert into #tblA select 3,30 ,30
insert into #tblA select 4,500 ,500
insert into #tblA select 5,200 ,200
insert into #tblA select 6,1000 ,1000declare @fAmount as decimal(18,2)
set @fAmount=1500
update
a
set
@fAmount = case when @fAmount<=0.0 then 0.0 else @fAmount - fAmountBal end,
fAmountBal = case when @fAmount<=0 then fAmountBal-@fAmount else 0 end
from
#tblA aselect * from #tblA--输出结果
noid fAmount fAmountBal
---- ------- ----------
1 200.00 0.00
2 100.20 0.00
3 30.00 0.00
4 500.00 0.00
5 200.00 0.00
6 1000.00 1530.20