编写SQL查询实现PO金额按规则分摊至业务金额

现有数据表 #

Table 1（账单表） #

Table 2（PO表） #

需求说明 #

需要编写SQL关联两张表，将Table2的PO_Amount按Sequence顺序分摊至Table1的Amount列，生成计算列PO_Alloc_Amount（本次分摊的PO金额）和PO_Remaining_Amount（该PO分摊后剩余的金额），预期结果如下：

预期结果 #

分摊规则 #

• Table1中ID=1的账单金额为5000，优先使用PO1的2000，剩余3000从PO2中抵扣；
• PO2抵扣后剩余的1000用于ID=2的账单金额；
• ID=3的账单无可用PO金额，相关列显示null。

实现SQL语句 #

以下是基于MySQL 8.0+（支持窗口函数）的实现，核心逻辑是通过计算累计金额区间，匹配账单与PO的分摊关系：

WITH 
-- 计算账单的累计需求区间
bill_cumulative AS (
    SELECT 
        ID,
        Amount,
        SUM(Amount) OVER (ORDER BY ID) AS cum_bill,
        SUM(Amount) OVER (ORDER BY ID) - Amount AS prev_cum_bill
    FROM Table1
),
-- 计算PO的累计可分摊区间
po_cumulative AS (
    SELECT 
        PO,
        PO_Amount,
        Sequence,
        SUM(PO_Amount) OVER (ORDER BY Sequence) AS cum_po,
        SUM(PO_Amount) OVER (ORDER BY Sequence) - PO_Amount AS prev_cum_po
    FROM Table2
),
-- 匹配有金额交集的账单与PO对，计算分摊金额
allocation_matches AS (
    SELECT 
        bc.ID,
        bc.Amount,
        pc.PO,
        pc.PO_Amount,
        pc.Sequence,
        GREATEST(
            0,
            LEAST(bc.cum_bill, pc.cum_po) - GREATEST(bc.prev_cum_bill, pc.prev_cum_po)
        ) AS PO_Alloc_Amount
    FROM bill_cumulative bc
    CROSS JOIN po_cumulative pc
    WHERE LEAST(bc.cum_bill, pc.cum_po) > GREATEST(bc.prev_cum_bill, pc.prev_cum_po)
),
-- 计算每个PO分摊后的剩余金额
po_remaining AS (
    SELECT 
        PO,
        ID,
        PO_Amount - SUM(PO_Alloc_Amount) OVER (PARTITION BY PO ORDER BY ID) AS PO_Remaining_Amount
    FROM allocation_matches
)
-- 合并结果，补充无PO分摊的账单记录
SELECT 
    bc.ID,
    bc.Amount,
    am.PO,
    am.PO_Amount,
    am.PO_Alloc_Amount,
    pr.PO_Remaining_Amount
FROM bill_cumulative bc
LEFT JOIN allocation_matches am ON bc.ID = am.ID
LEFT JOIN po_remaining pr ON am.PO = pr.PO AND am.ID = pr.ID
ORDER BY bc.ID, COALESCE(am.Sequence, 0);

逻辑说明 #

1. bill_cumulative：计算每个账单的累计金额，确定当前账单的需求覆盖区间（prev_cum_bill到cum_bill）；
2. po_cumulative：计算每个PO的累计金额，确定当前PO的可分摊覆盖区间（prev_cum_po到cum_po）；
3. allocation_matches：通过区间交叉筛选出有分摊关系的账单-PO对，计算具体分摊金额；
4. po_remaining：针对每个PO，按账单顺序累计已分摊金额，得出剩余可用金额；
5. 最后通过左连接保留所有账单记录，按ID和PO顺序排序输出。

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