You need to enable JavaScript to run this app. SQL Server 无循环单SELECT实现按银行优先级的单据额度分配
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.24 09:06:04