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

Question

我有一个包含几个表的数据库，这些表跟踪电话/短信/数据和配额，并且我正在尝试计算是否可以在不使用游标的情况下将配额分配给配额，但是我想不通一种方法构造SQL来做到这一点。我的尝试中没有任何有用的SQL，因为我似乎无法掌握如何使用它的方法！问题在于，对我而言，这似乎是一个固有的迭代过程，如果有明智的方法将其转换为基于集合的方法，我将无法解决。我已经考虑过使用开窗函数，但是当我们跟踪2个表中的累计总数并且总数是相互依赖的时，我看不到该怎么做。我正在尽量减少运行此过程的时间以及对其他查询的影响，因为

这是一个简化的结构…

称呼

记录所有通话

• ID
• 合约编号
• ChargeGroupID
• 约会时间
• 数量int
• QuantityFromAllowances int（这是我要填充的内容）
• FirstAllowanceUsedID（从FK到津贴）（这是我要填充的内容）

津贴

每份合约有哪些不同的备抵金

• ID
• 合约编号
• 优先级（如果首先使用，则为1，否则为0）
• 数量int
• QuantityUsed int（最初设置为0-可以跟踪我们使用或不使用的数量）

津贴组

如何使用津贴-这是一个联结表，列出了允许的组合

• ID
• 津贴ID
• ChargeGroupID

为了简化起见，我故意没有记录所有细节。我希望一切都显而易见，但如果没有，请告诉我。

如果我反复处理此问题，我的伪代码将类似于：-

For each Call ordered by DateTime
    Declare a as Allowance
    Do

        Set a = First Allowance Where Allowance.ContractID=Call.ContractID And Allowance.QuantityUsed<Allowance.Quantity Order by Priority Descending
        If a != NULL
            Declare n as Integer
            Set n = a.Quantity-a.QuantityUsed
            If Call.Quantity-Call.QuantityFromAllowances<n
                Set n = Call.Quantity-Call.QuantityFromAllowances
            End if
            Set Call.QuantityFromAllowances = Call.QuantityFromAllowances + n
            If Call.FirstAllowanceUsedID == NULL Then 
                Set Call.FirstAllowanceUsedID = a.ID
            End if
            Set a.QuantityUsed = a.QuantityUsed + n

        End if

    Loop while a != NULL AND Call.QuantityFromAllowances<Call.Quantity

Next Call

随时告诉我我正在错误地解决问题，或者这实际上是游标的一个很好的候选人。我只是在寻找最佳解决方案。

举个例子： -

称呼
ID合同ID ChargeGroupID DateTime数量QuantityFromAllowances FirstAllowanceUsedID 
1 1 1 2016-11-01 100 0空
2 1 2 2016-11-02 500 0空
3 1 1 2016-11-03 500 0空
4 1 3 2016-11-04 100 0空
5 1 1 2016-11-05 100 0空
6 2 1 2016-11-01 100 0空

津贴
ID合同ID优先数量使用的数量
1 1 1 500 0
2 1 0 500 0
3 2 1 500 0
4 2 0 500 0

津贴组
ID配额ID ChargeGroupID
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1

在我的示例中，我将其计算如下：-

1. 呼叫ID 1与配额ID 1匹配（通过AllowanceChargeGroup中的联结表）-QuantityFromAllowances = 100，FirstAllowanceUsedID = 1，Allowance.QuantityUsed = 100（0 + 100）
2. 呼叫ID 2与配额ID 1相匹配，但配额中只剩下400，因此，QuantityFromAllowances = 400，FirstAllowanceUsedID = 1，Allowance.QuantityUsed = 500（100 + 400）
3. 呼叫ID 2匹配配额ID 2（1上不剩）-QuantityFromAllowances = 500（400 + 100），FirstAllowanceUsedID = 1（已在上面设置，因此未更改），Allowance.QuantityUsed = 100（0 + 100）
4. 呼叫ID 3与配额ID 2（未保留1）匹配-，但配额中仅剩400，因此，QuantityFromAllowances = 400，FirstAllowanceUsedID = 2，Allowance.QuantityUsed = 500（100 + 400）。
5. 呼叫ID 4与任何配额都不匹配，因此没有变化
6. 呼叫ID 5与任何配额都不匹配（全部用光），因此没有变化
7. 呼叫ID 6与Allowance ID 3匹配allowanceFromAllowances = 100，FirstAllowanceUsedID = 3，Allowance.QuantityUsed = 100（0 + 100）

之后，表格应如下所示（仅更改为Call.QuantityFromAllowances，Call.FirstAllowanceUsedID，Allowance.QuantityUsed
…

称呼
ID合同ID ChargeGroupID DateTime数量QuantityFromAllowances FirstAllowanceUsedID 
1 1 1 2016-11-01 100 100 1
2 1 2 2016-11-02 500 500 1
3 1 1 2016-11-03 500 400 2
4 1 3 2016-11-04 100 0空
5 1 1 2016-11-05 100 0空
6 2 1 2016-11-01 100 100 3

津贴
ID合同ID优先数量使用的数量
1 1 1 500 500
2 1 0 500 500
3 2 1 500 100
4 2 0 500 0

津贴组
ID配额ID ChargeGroupID
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1

Answer 1

您要同时更新呼叫表和限额表，并且每次更新都取决于上一个。
仅使用一条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 list
insert into @todo
select id, Quantity-QuantityFromAllowances, 0
from [call]
where Quantity>QuantityFromAllowances

然后主循环波谷调用：

set @id1=0
set @q1= null
while 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
    end
end

最后是输出：

select * from [call]
select * from allowance

与要求相同