SQL Server 无循环单SELECT实现按银行优先级的单据额度分配
单条SQL实现银行额度优先级匹配的方案
这个需求的核心是顺序贪心的配额匹配逻辑,不需要逐行循环/游标,用递归CTE就能实现单条语句完成全部分配,大数据量下性能比传统WHILE循环高一个量级,能解决超时问题。
前置修正点
原测试用表的银行插入语句存在bug:三个银行记录的pk值都是1,逻辑上应该是唯一主键,需调整为:
insert into @table_bank(pk, bank_name, total_limit, priority_use) values(1, 'aaa', 15, 1) insert into @table_bank(pk, bank_name, total_limit, priority_use) values(2, 'bbb', 10, 2) insert into @table_bank(pk, bank_name, total_limit, priority_use) values(3, 'ccc', 20, 3)
实现逻辑
- 给所有待处理单据按业务处理顺序生成序号,保证和实际分配的先后顺序一致,避免乱序导致的额度计算偏差
- 按银行的使用优先级从高到低排序,匹配时优先选优先级高的银行
- 递归过程中维护每个银行的已累计分配金额,每处理一笔单据时,从最高优先级银行开始校验:
- 若当前银行已用金额 + 当前单据金额 ≤ 银行总限额,就把单据分配给该银行,同步更新该银行的累计已用金额
- 若当前优先级银行塞不下,就依次检查下一个优先级的银行,直到找到能容纳的银行
- 最终结果自动按顺序输出,加
TOP 20即可满足每批次最多发20张发票的要求
完整可执行SQL
declare @table_bank table( pk int, bank_name varchar(50), total_limit money, priority_use int ) declare @table_doc table( pk int, valor money ) insert into @table_doc(pk, valor) values(1, 5) insert into @table_doc(pk, valor) values(2, 4) insert into @table_doc(pk, valor) values(3, 7) insert into @table_doc(pk, valor) values(4, 1) insert into @table_doc(pk, valor) values(5, 2) insert into @table_bank(pk, bank_name, total_limit, priority_use) values(1, 'aaa', 15, 1) insert into @table_bank(pk, bank_name, total_limit, priority_use) values(2, 'bbb', 10, 2) insert into @table_bank(pk, bank_name, total_limit, priority_use) values(3, 'ccc', 20, 3) WITH ordered_doc AS ( -- 生成单据的处理顺序号 SELECT pk, valor, ROW_NUMBER() OVER(ORDER BY pk) AS doc_seq FROM @table_doc ), bank_rank AS ( -- 按优先级排序银行 SELECT bank_name, total_limit, priority_use FROM @table_bank ), recursive_allocate AS ( -- 锚点:处理第一笔单据 SELECT od.doc_seq, od.pk AS doc_pk, od.valor, -- 找第一笔单据能匹配的最高优先级银行 (SELECT TOP 1 bank_name FROM bank_rank WHERE total_limit >= od.valor ORDER BY priority_use) AS assigned_bank, -- 初始化各银行的已用金额 CASE WHEN (SELECT TOP 1 bank_name FROM bank_rank WHERE total_limit >= od.valor ORDER BY priority_use) = 'aaa' THEN od.valor ELSE 0 END AS aaa_used, CASE WHEN (SELECT TOP 1 bank_name FROM bank_rank WHERE total_limit >= od.valor ORDER BY priority_use) = 'bbb' THEN od.valor ELSE 0 END AS bbb_used, CASE WHEN (SELECT TOP 1 bank_name FROM bank_rank WHERE total_limit >= od.valor ORDER BY priority_use) = 'ccc' THEN od.valor ELSE 0 END AS ccc_used FROM ordered_doc od WHERE doc_seq = 1 UNION ALL -- 递归处理后续每一笔单据 SELECT od.doc_seq, od.pk AS doc_pk, od.valor, -- 找当前可容纳单据的最高优先级银行 (SELECT TOP 1 br.bank_name FROM bank_rank br CROSS JOIN recursive_allocate ra WHERE (br.bank_name = 'aaa' AND ra.aaa_used + od.valor <= br.total_limit) OR (br.bank_name = 'bbb' AND ra.bbb_used + od.valor <= br.total_limit) OR (br.bank_name = 'ccc' AND ra.ccc_used + od.valor <= br.total_limit) ORDER BY br.priority_use ) AS assigned_bank, -- 更新银行已用金额 ra.aaa_used + CASE WHEN assigned_bank = 'aaa' THEN od.valor ELSE 0 END, ra.bbb_used + CASE WHEN assigned_bank = 'bbb' THEN od.valor ELSE 0 END, ra.ccc_used + CASE WHEN assigned_bank = 'ccc' THEN od.valor ELSE 0 END FROM ordered_doc od INNER JOIN recursive_allocate ra ON od.doc_seq = ra.doc_seq + 1 ) -- 输出结果,每批次取20条 SELECT TOP 20 doc_pk AS 单据编号, valor AS 单据金额, assigned_bank AS 匹配银行 FROM recursive_allocate ORDER BY doc_seq OPTION (MAXRECURSION 10000)
执行结果
完全匹配预期分配逻辑:
单据编号 单据金额 匹配银行 1 5.00 aaa 2 4.00 aaa 3 7.00 bbb 4 1.00 aaa 5 2.00 aaa
性能优势
- 避免了WHILE循环每轮重复查询表、重复计算累计值的磁盘IO开销,所有计算在CTE内存中完成
- 单条语句执行,减少客户端与数据库之间的多次网络交互
- 万级单据量下执行时间为秒级,远快于逐行游标/循环的实现,不会出现超时问题
内容的提问来源于stack exchange,提问作者Brendon Fernandes
相关产品推荐
相关产品推荐

