如何用SQL递归逻辑实现跨payseq的递进式金额分配？

新增paidamount列，按acctid+payseq组合将totalamount分配给acctnum，分配需延续上一payseq的剩余金额断点：

• payseq=99时：1000.00元分配给前2个acctnum，1111aa得800.00元，1111bb得200.00元；
• payseq=100时：从1111bb的600.00元剩余缺口开始分配2000.00元，覆盖1111bb、1111cc、1111dd；
• 后续payseq需按此逻辑延续。

用以下窗口函数实现时，每次都会从第一个acctnum开始分配，无法延续上一payseq的断点：

totalamount - sum(ifnull(ExpectPrice,0.0)) 
over(partition by acctId, payseq order by payorder asc rows between unbounded preceding and 0 preceding) as PaidAmount

以下SQL通过递归CTE追踪每个acctnum的累计缺口和各payseq的剩余待分配金额，实现断点延续的分配逻辑（支持MySQL 8+、PostgreSQL、SQL Server等支持递归CTE的数据库）：

WITH ranked_data AS (
    -- 为每个acctid下的记录生成全局排序序号，保证递归处理顺序
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY acctid ORDER BY payseq, payorder) AS global_row
    FROM your_table_name
),
recursive_alloc AS (
    -- 递归起始：处理第一条记录
    SELECT
        acctid,
        payseq,
        totalamount,
        acctnum,
        expectprice,
        payorder,
        LEAST(totalamount, expectprice) AS paidamount,
        GREATEST(totalamount - expectprice, 0) AS remaining_total,
        GREATEST(expectprice - totalamount, 0) AS remaining_expect
    FROM ranked_data
    WHERE global_row = 1
    
    UNION ALL
    
    -- 递归处理后续记录
    SELECT
        rd.acctid,
        rd.payseq,
        rd.totalamount,
        rd.acctnum,
        rd.expectprice,
        rd.payorder,
        -- 计算当前记录的分配金额
        CASE
            -- 上一批次还有剩余待分配金额
            WHEN ra.remaining_total > 0 THEN
                LEAST(ra.remaining_total + rd.totalamount, 
                      -- 同payseq则用当前expectprice，不同则用上一acctnum的剩余缺口
                      rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect))
            ELSE
                LEAST(rd.totalamount, 
                      rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect))
        END AS paidamount,
        -- 计算分配后剩余的待分配总金额
        CASE
            WHEN ra.remaining_total > 0 THEN
                GREATEST(ra.remaining_total + rd.totalamount - 
                         (rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect)), 0)
            ELSE
                GREATEST(rd.totalamount - 
                         (rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect)), 0)
        END AS remaining_total,
        -- 计算当前acctnum分配后的剩余缺口
        CASE
            WHEN ra.remaining_total > 0 THEN
                GREATEST((rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect)) - 
                         (ra.remaining_total + rd.totalamount), 0)
            ELSE
                GREATEST((rd.expectprice - IF(rd.payseq = ra.payseq, 0, ra.remaining_expect)) - 
                         rd.totalamount, 0)
        END AS remaining_expect
    FROM ranked_data rd
    JOIN recursive_alloc ra ON rd.acctid = ra.acctid AND rd.global_row = ra.global_row + 1
)
-- 输出最终结果
SELECT
    acctid,
    payseq,
    totalamount,
    acctnum,
    expectprice,
    payorder,
    paidamount
FROM recursive_alloc
ORDER BY acctid, payseq, payorder;

逻辑说明 #

1. ranked_data：给每个acctid的记录按payseq、payorder升序排序，生成全局唯一序号，确保递归按业务顺序处理。
2. recursive_alloc：
   • 起始节点处理第一条记录，计算首次分配金额、剩余待分配总金额和当前acctnum的未填满缺口。
   • 递归节点中，判断当前记录的payseq是否与上一条一致：
     • 一致则属于同一批次分配，直接用剩余待分配金额填充当前acctnum的缺口。
     • 不一致则属于下一批次，以上一批次结束时的acctnum剩余缺口为起点，结合当前批次的totalamount继续分配。
3. 最终结果按acctid、payseq、payorder排序，得到符合要求的paidamount列。

内容的提问来源于stack exchange，提问作者Vikraman