如何在SQL中实现付款记录与发票/费用账单的按序分配？

发票数据表（invoice_data） #

付款数据表（payment_data） #

需求说明 #

需要将付款按优先分配给最早的发票的规则进行分配：付清当前最早的未结清发票后，剩余付款金额再分配给下一笔最早的发票，以此类推，最终得到每笔付款对应分配到各发票的金额。

预期结果 #

SQL实现方案 #

核心思路是先为每个客户的发票和付款计算累计金额，再通过区间匹配确定付款的分配对象和金额（兼容PostgreSQL、SQL Server、MySQL 8+等支持窗口函数的数据库）：

WITH invoice_with_running_total AS (
    SELECT 
        customer_id,
        scheduled_payment_date,
        scheduled_total_payment,
        SUM(scheduled_total_payment) OVER (
            PARTITION BY customer_id 
            ORDER BY scheduled_payment_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS invoice_running_total,
        SUM(scheduled_total_payment) OVER (
            PARTITION BY customer_id 
            ORDER BY scheduled_payment_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_invoice_running_total
    FROM invoice_data
),
payment_with_running_total AS (
    SELECT 
        customer_id,
        payment_date,
        total_payment,
        SUM(total_payment) OVER (
            PARTITION BY customer_id 
            ORDER BY payment_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS payment_running_total,
        SUM(total_payment) OVER (
            PARTITION BY customer_id 
            ORDER BY payment_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_payment_running_total
    FROM payment_data
),
payment_invoice_matches AS (
    SELECT 
        p.customer_id,
        p.payment_date,
        p.total_payment,
        i.scheduled_payment_date,
        i.scheduled_total_payment,
        -- 计算当前付款可分配给当前发票的金额
        GREATEST(
            0,
            LEAST(
                p.payment_running_total, i.invoice_running_total
            ) - GREATEST(
                p.prev_payment_running_total, COALESCE(i.prev_invoice_running_total, 0)
            )
        ) AS payment_allocation
    FROM payment_with_running_total p
    JOIN invoice_with_running_total i 
        ON p.customer_id = i.customer_id
        -- 匹配付款累计区间与发票累计区间有重叠的记录
        AND p.payment_running_total > COALESCE(i.prev_invoice_running_total, 0)
        AND p.prev_payment_running_total < i.invoice_running_total
)
SELECT 
    customer_id,
    payment_date,
    scheduled_payment_date,
    total_payment,
    payment_allocation,
    scheduled_total_payment
FROM payment_invoice_matches
WHERE payment_allocation > 0
ORDER BY customer_id, payment_date, scheduled_payment_date;

代码解释 #

1. invoice_with_running_total：为每个客户的发票按日期排序，计算累计金额（invoice_running_total）和上一笔累计金额（prev_invoice_running_total），标记每笔发票对应的金额区间。
2. payment_with_running_total：同理，为每个客户的付款按日期排序，计算累计金额和上一笔累计金额，标记每笔付款对应的金额区间。
3. payment_invoice_matches：将付款和发票按客户关联，匹配两者累计金额有重叠的记录，通过区间计算得出该付款分配到对应发票的金额。
4. 最后过滤掉分配金额为0的记录，按客户、付款日期、发票日期排序得到结果。

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