create table tba(cardid varchar(10),cid int,amount decimal(18,2))
insert into tba
select '1001' ,201 ,37.57 union all
select '1001' ,207 ,21.44 union all
select '1001' ,217 ,131.96 union all
select '1002' ,201 ,31.43 union all
select '1002' ,207 ,25.40 union all
select '1003' ,207 ,29.11
go

create table tbb(cardid varchar(10),amount decimal(18,2))
insert into tbb
select '1001' ,100.00 union all
select '1002' ,80.00
go

declare @cardid varchar(10)
declare @amount decimal(18,2)
declare @cat decimal(18,2)

select * into #tba
from (
    select cardid,sum(amount) amount from tba group by cardid
) t

update a
set @amount = (case when b.cardid <> isnull(@cardid,'') then b.amount else @amount end),
    @cat = (case when b.cardid <> isnull(@cardid,'') then 0 else @cat end),
    @cardid = b.cardid,
    @amount = @amount - @cat,
    a.amount = (case when @amount >=0 then
                        case when @amount >= a.amount then 0
                             when @amount < a.amount then a.amount - @amount end
                    else a.amount end),
    @cat = a.amount
from tbb b join tba a on b.cardid = a.cardid
where a.amount <> 0 and b.amount <> 0

select * from tba

update b
set b.amount = (case when b.amount < isnull(a.amount,0) then 0
                     else b.amount - isnull(a.amount,0) end)
from #tba a join tbb b on a.cardid = b.cardid

select * from tbb

drop table tba,tbb,#tba

/**********************

cardid     cid         amount
---------- ----------- ---------------------------------------
1001       201         0.00
1001       207         0.00
1001       217         90.97
1002       201         0.00
1002       207         0.00
1003       207         29.11

(6 行受影响)

(2 行受影响)

cardid     amount
---------- ---------------------------------------
1001       0.00
1002       23.17

(2 行受影响)