对2个相互依赖的表进行复杂的SQL更新

对2个相互依赖的表进行复杂的SQL更新

您要同时更新呼叫表和限额表，并且每次更新都取决于上一个。
仅使用一条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

与要求相同