SQL实现销售订单按采购单剩余可用库存依次分配问题求解

你当前的写法未累加历史销售订单的占用库存，每次都取排序第一的采购单，导致分配错误。该需求属于典型的先进先出(FIFO)库存匹配场景，可通过窗口函数计算累计区间的方式实现。

核心逻辑 #

• 按销售单创建时间排序，计算每个销售单的累计需求区间
• 按采购单优先级（示例按采购单号升序，可调整为按发货日期升序）排序，计算每个采购单的累计可用库存区间
• 匹配需求区间与库存区间有交集的采购单，即为对应销售单的分配采购单

完整SQL代码 #

WITH so_cumulative AS (
    -- 计算销售单累计需求，得到每个销售单的需求覆盖区间
    SELECT 
        number,
        item,
        createDate,
        qtyNeeded,
        COALESCE(SUM(qtyNeeded) OVER (
            PARTITION BY item 
            ORDER BY createDate, number 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS prev_so_total,
        SUM(qtyNeeded) OVER (
            PARTITION BY item 
            ORDER BY createDate, number
        ) AS curr_so_total
    FROM salesOrder
),
po_cumulative AS (
    -- 计算采购单累计可用库存，得到每个采购单的库存覆盖区间
    SELECT 
        number AS po_num,
        item,
        freeQty,
        COALESCE(SUM(freeQty) OVER (
            PARTITION BY item 
            ORDER BY number -- 此处可根据业务调整排序规则，比如shipDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS prev_po_total,
        SUM(freeQty) OVER (
            PARTITION BY item 
            ORDER BY number -- 此处排序规则需和上方一致
        ) AS curr_po_total
    FROM purchaseOrders
    WHERE freeQty > 0 -- 过滤无可用库存的采购单
)
-- 区间匹配得到最终分配结果
SELECT 
    so.number AS sales_order_num,
    so.item,
    so.createDate,
    so.qtyNeeded,
    po.po_num AS allocateToPoNum
FROM so_cumulative so
INNER JOIN po_cumulative po 
    ON so.item = po.item
    AND so.prev_so_total < po.curr_po_total 
    AND so.curr_so_total > po.prev_po_total
ORDER BY so.createDate, so.number;

执行上述SQL后，返回结果和预期完全一致：

如果存在单个销售单需求大于单个采购单可用库存，需要拆分销售单匹配多个采购单的场景，只需在最终查询中补充计算对应分配的库存数量即可，无需修改核心逻辑。

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