2 个相互依赖的表上的复杂 SQL 更新 [英] Complex SQL Update on 2 interdependent tables
问题描述
我有一个包含多个表的数据库，用于跟踪电话/短信/数据和津贴，我正在尝试确定是否可以在不使用游标的情况下将通话分配给津贴，但我无法弄清楚一种构造 SQL 的方法.我的尝试中没有任何有用的 SQL，因为我似乎无法理解如何处理它！问题是，对我来说，这似乎是一个固有的迭代过程，我不知道是否有一种明智的方法可以将其转化为基于集合的方法.我已经考虑过使用窗口函数，但是当我们在 2 个表中跟踪累积总数并且总数是相互依赖的时，我看不到如何做到这一点.我正在尽量减少运行此过程的时间以及对其他查询的影响，因为我们希望相当频繁地重新运行它并且表变得非常大.
这是一个简化的结构...
打电话
记录所有调用
- 身份证
- 合同编号
- 费用组 ID
- 日期时间
- 数量统计
- QuantityFromAllowances int(这是我想要填充的)
- FirstAllowanceUsedID(FK to Allowance)(这是我想要填充的)
津贴
每份合同有哪些不同的津贴
- 身份证
- 合同编号
- 优先级(如果先使用则为 1，否则为 0)
- 数量统计
- QuantityUsed int(最初设置为 0 - 可用于跟踪我们使用或不使用的数量)
AllowanceChargeGroup
如何允许使用配额 - 这是一个列出允许组合的连接表
- 身份证
- AllowanceID
- 费用组 ID
为了简单起见，我故意没有记录所有细节.我希望一切都很明显，但如果没有，请告诉我.
如果我以迭代方式处理这个问题，我的伪代码将类似于:-
对于按 DateTime 排序的每个 Call将 a 声明为津贴做设置 a = First Allowance Where Allowance.ContractID=Call.ContractID And Allowance.QuantityUsed请随时告诉我我处理问题的方式有误，或者这实际上是游标的一个很好的候选者.我只是在寻找最佳解决方案.
例如:-
<前>称呼ID ContractID ChargeGroupID DateTime QuantityFromAllowances FirstAllowanceUsedID1 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 ContractID 优先数量 QuantityUsed1 1 1 500 02 1 0 500 03 2 1 500 04 2 0 500 0津贴收费组ID AllowanceID ChargeGroupID1 1 12 1 23 2 14 2 25 3 1在我的例子中，我会按如下方式计算:-
- Call ID 1 与 Allowance ID 1 匹配(通过 AllowanceChargeGroup 中的连接表)- QuantityFromAllowances=100、FirstAllowanceUsedID=1、Allowance.QuantityUsed=100 (0+100)
- Call ID 2 与 Allowance ID 1 匹配，但只剩下 400 个允许使用，因此 QuantityFromAllowances=400、FirstAllowanceUsedID=1、Allowance.QuantityUsed=500 (100+400)
- 呼叫 ID 2 与 Allowance ID 2 匹配(1 上没有剩余)- QuantityFromAllowances=500 (400+100)、FirstAllowanceUsedID=1(已在上面设置，因此未更改)、Allowance.QuantityUsed=100 (0+100)
- Call ID 3 与 Allowance ID 2 匹配(在 1 上没有剩余) - ，但仍然只剩下 400 个允许使用，因此 QuantityFromAllowances=400、FirstAllowanceUsedID=2、Allowance.QuantityUsed=500 (100+400).
- Call ID 4 与任何限额不匹配，所以没有变化
- Call ID 5 不匹配任何限额(全部用完)所以没有变化
- Call ID 6 匹配 Allowance ID 3 QuantityFromAllowances=100, FirstAllowanceUsedID=3, Allowance.QuantityUsed=100 (0+100)
之后，表格应如下所示(仅更改为 Call.QuantityFromAllowances、Call.FirstAllowanceUsedID、Allowance.QuantityUsed...
<前>称呼ID ContractID ChargeGroupID DateTime QuantityFromAllowances FirstAllowanceUsedID1 1 1 2016-11-01 100 100 12 1 2 2016-11-02 500 500 13 1 1 2016-11-03 500 400 24 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 ContractID 优先数量 QuantityUsed1 1 1 500 5002 1 0 500 5003 2 1 500 1004 2 0 500 0津贴收费组ID AllowanceID ChargeGroupID1 1 12 1 23 2 14 2 25 3 1您想同时更新调用表和配额表，每次更新都依赖于前一个.
仅使用一个 sql 语句是不可能的，因此您需要循环.
你不需要游标，你可以用一个过程中的顺序集合操作来解决它.
首先进行一些声明并准备一些数据:
声明@todo为表(callID int主键，qt int，done bit，unique (done, qt, callid))声明@id1 int、@id2 int、@q1 int、@q2 int-- 准备工作清单插入@todo选择 id, Quantity-QuantityFromAllowances, 0来自 [呼叫]其中 Quantity>QuantityFromAllowances然后主循环通过调用:
set @id1=0设置@q1=空而不是(@id1 为空)开始设置@id1=nullselect top 1 @id1 = callID, @q1=qt from @todo where done=0 and qt>0 order by callID如果不是(@id1 为空)开始设置@id2 = null选择前 1 @id2 = a.id, @q2 = a.Quantity - a.QuantityUsed来自 [呼叫] cg.ChargeGroupID = c.ChargeGroupID 上的内部连接 AllowanceChargeGroup g内连接允许 a on (a.ID = g.AllowanceID) 和 (a.Quantity>a.QuantityUsed)其中 c.ID=@id1order by c.ID,[Priority] desc, (a.Quantity-a.QuantityUsed) desc如果不是(@id2 为空)开始如果@q2 <@q1 设置 @q1 = @q2更新一组 QuantityUsed = QuantityUsed + @q1从津贴 a其中 a.ID=@id2更新 c 设置 QuantityFromAllowances = QuantityFromAllowances + @q1, FirstAllowanceUsedID = isnull(FirstAllowanceUsedID, @id2)来自 [呼叫] c其中 c.ID=@id1更新 t 集 qt = qt-@q1, done = IIF(qt-@q1=0,1,0)来自@todo t其中 t.callID=@id1结束其他开始--无法完成更新 t 设置完成 = 1来自@todo t其中 t.callID=@id1结尾结尾结尾最后是输出:
select * from [call]从津贴中选择 *与要求相同
I have a database with several tables keeping track of phone calls/sms/data and allowances and I'm trying to work out if it is possible to allocate calls to allowances without resorting to cursors, but I can't figure out a way of structuring the SQL to do so. I don't have any useful SQL from my attempts as I can't seem to get my head around how to approach it! The problem is that to me this seems like an inherently iterative process and I can't work out if there is a sensible way to translate it into a set based approach. I've considered using windowing functions, but I can't see how to do that when we're tracking cumulative totals in 2 tables and the totals are interdependent. I'm trying to minimise the time to run this process and the impact on other queries as we'd like to rerun it fairly frequently and the tables are getting pretty big.
This is a simplified structure...
Call
logs all of the calls
- ID
- ContractID
- ChargeGroupID
- DateTime
- Quantity int
- QuantityFromAllowances int (this is what I want to populate)
- FirstAllowanceUsedID (FK to Allowance) (this is what I want to populate)
Allowance
What different allowances are available on each contract
- ID
- ContractID
- Priority (1 if it is to be used first, otherwise 0)
- Quantity int
- QuantityUsed int (initially set to 0 - can be used to keep track of how much is used as we go or not)
AllowanceChargeGroup
How the allowances are allowed to be used - this is a junction table listing allowable combinations
- ID
- AllowanceID
- ChargeGroupID
I've intentionally not documented all details to keep it simple. I hope everything is obvious but if not then let me know.
If I was dealing with this iteratively, my psueodocode would be something like: -
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
Feel free to tell me that I'm approaching the problem wrong or that this actually is a good candidate for cursors. I'm just looking for the best solution.
As an example: -
Call ID ContractID ChargeGroupID DateTime Quantity QuantityFromAllowances FirstAllowanceUsedID 1 1 1 2016-11-01 100 0 NULL 2 1 2 2016-11-02 500 0 NULL 3 1 1 2016-11-03 500 0 NULL 4 1 3 2016-11-04 100 0 NULL 5 1 1 2016-11-05 100 0 NULL 6 2 1 2016-11-01 100 0 NULL Allowance ID ContractID Priority Quantity QuantityUsed 1 1 1 500 0 2 1 0 500 0 3 2 1 500 0 4 2 0 500 0 AllowanceChargeGroup ID AllowanceID ChargeGroupID 1 1 1 2 1 2 3 2 1 4 2 2 5 3 1
In my example, I would calculate it as follows: -
- Call ID 1 matches Allowance ID 1 (via junction table in AllowanceChargeGroup) - QuantityFromAllowances=100, FirstAllowanceUsedID=1, Allowance.QuantityUsed=100 (0+100)
- Call ID 2 matches Allowance ID 1, but only 400 still left on allowance, so QuantityFromAllowances=400, FirstAllowanceUsedID=1, Allowance.QuantityUsed=500 (100+400)
- Call ID 2 matches Allowance ID 2 (none left on 1) - QuantityFromAllowances=500 (400+100), FirstAllowanceUsedID=1 (already set above so not changed), Allowance.QuantityUsed=100 (0+100)
- Call ID 3 matches Allowance ID 2 (none left on 1) - , but only 400 still left on allowance, so QuantityFromAllowances=400, FirstAllowanceUsedID=2, Allowance.QuantityUsed=500 (100+400).
- Call ID 4 does not match any allowances so no change
- Call ID 5 does not match any allowances (all used up) so no change
- Call ID 6 matches Allowance ID 3 QuantityFromAllowances=100, FirstAllowanceUsedID=3, Allowance.QuantityUsed=100 (0+100)
Afterwards, the tables should look like this (only changes are Call.QuantityFromAllowances, Call.FirstAllowanceUsedID, Allowance.QuantityUsed...
Call ID ContractID ChargeGroupID DateTime Quantity 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 NULL 5 1 1 2016-11-05 100 0 NULL 6 2 1 2016-11-01 100 100 3 Allowance ID ContractID Priority Quantity QuantityUsed 1 1 1 500 500 2 1 0 500 500 3 2 1 500 100 4 2 0 500 0 AllowanceChargeGroup ID AllowanceID ChargeGroupID 1 1 1 2 1 2 3 2 1 4 2 2 5 3 1
You want to update both call table and allowance table, and each update depends on previous one.
This is not possible with only one sql statement so you need to loop.
You do not need cursors, you can settle it with sequential set operations in a procedure.
First of all some declaration and prepare some data:
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
Then main loop trough calls:
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
And finally the output:
select * from [call]
select * from allowance
same as requested
这篇关于2 个相互依赖的表上的复杂 SQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
