您要同时更新呼叫表和限额表，并且每次更新都取决于上一个。
仅使用一条sql语句是不可能的，因此您需要循环。
您不需要游标，可以在过程中使用顺序设置操作来解决它。
首先进行一些声明并准备一些数据：
declare @todo as table (callID int primary key, qt int, done bit, unique (done, qt, callid))declare @id1 int, @id2 int, @q1 int, @q2 int-- prepare job listinsert into @todoselect id, Quantity-QuantityFromAllowances, 0from [call]where Quantity>QuantityFromAllowances
然后主循环波谷调用：
set @id1=0set @q1= nullwhile not(@id1 is null) begin set @id1=null select top 1 @id1 = callID, @q1=qt from @todo where done=0 and qt>0 order by callID if not(@id1 is null) begin set @id2 = null select top 1 @id2 = a.id, @q2 = a.Quantity - a.QuantityUsed from [call] c inner join AllowanceChargeGroup g on g.ChargeGroupID = c.ChargeGroupID inner join allowance a on (a.ID = g.AllowanceID) and (a.Quantity>a.QuantityUsed) where c.ID=@id1 order by c.ID,[Priority] desc, (a.Quantity-a.QuantityUsed) desc if not(@id2 is null) begin if @q2 < @q1 set @q1 = @q2 update a set QuantityUsed = QuantityUsed + @q1 from allowance a where a.ID=@id2 update c set QuantityFromAllowances = QuantityFromAllowances + @q1, FirstAllowanceUsedID = isnull(FirstAllowanceUsedID, @id2) from [call] c where c.ID=@id1 update t set qt = qt-@q1, done = IIF(qt-@q1=0,1,0) from @todo t where t.callID=@id1 end else begin -- unable to complete update t set done = 1 from @todo t where t.callID=@id1 end endend
最后是输出：
select * from [call]select * from allowance
与要求相同
