匹配客户订单对应履约采购单号的SQL实现方案咨询

实现逻辑说明 #

我们采用先进先出(FIFO)匹配规则，按订单生成顺序匹配最早到货的可用采购库存，核心逻辑是分别计算客户订单的累计履约需求量、采购订单的累计可用库存量，再通过范围匹配完成对应关系绑定。

完整SQL代码 #

WITH customer_orders AS (
    -- 计算所有客户订单的累计需求量，按订单号升序（即下单先后顺序）
    SELECT 
        订单编号,
        SUM(ABS(数量)) OVER (ORDER BY 订单编号) AS 累计需求
    FROM OrderTable
    WHERE 类型 = '客户订单'
),
purchase_orders AS (
    -- 计算所有采购订单的累计可用库存量，按订单号升序（即到货先后顺序）
    SELECT 
        订单编号 AS 采购单号,
        SUM(数量) OVER (ORDER BY 订单编号) AS 累计可用库存
    FROM OrderTable
    WHERE 类型 = '采购订单'
),
order_match AS (
    -- 为每个客户订单匹配满足累计需求的最早采购单
    SELECT 
        c.订单编号,
        MIN(p.采购单号) AS 履约对应采购单号
    FROM customer_orders c
    INNER JOIN purchase_orders p 
        ON p.累计可用库存 >= c.累计需求
    GROUP BY c.订单编号
)
-- 关联回原表输出最终结果
SELECT 
    t.订单编号,
    t.数量,
    t.库存,
    t.类型,
    m.履约对应采购单号
FROM OrderTable t
LEFT JOIN order_match m 
    ON t.订单编号 = m.订单编号
ORDER BY t.订单编号;

匹配结果验证 #

针对示例数据的匹配结果完全符合预期：

• 订单1001~1003累计需求≤采购单1006的3件库存，全部匹配1006
• 订单1004~1005剩余累计需求≤采购单1007的6件库存，全部匹配1007
• 采购订单本身无匹配单号，符合业务要求

适配说明 #

该方案支持所有兼容标准SQL窗口函数的数据库（MySQL8.0+、PostgreSQL、Spark SQL、Hive等），如果需要调整履约优先级，只需修改窗口函数的ORDER BY字段即可。

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