基于仓库库存的可执行订单识别SQL实现求助

DevOps 小满

2026-7-4

问题描述

现有两张表：

Warehouse：包含ProdId（库存产品ID）、ProdName（产品名称）字段，每条记录代表一个库存产品。
Orders：包含OrderNo（订单编号）、RequiredProdName（订单所需产品名称）字段，每条记录代表订单中的一个需求产品。

需求是识别可依据仓库库存完成的订单，需按顺序递归判断产品库存分配：

ORD001需要Prod007、Prod008、Prod009，库存充足，可执行；
ORD002需要Prod008、Prod009、Prod012，Prod009已分配给ORD001，库存不足，不可执行；
ORD003需要Prod008、Prod014，剩余库存充足，可执行。

最终需输出ORD001和ORD003。

解决方案

我们可以用递归CTE模拟库存的动态分配过程，按订单顺序逐个验证并更新剩余库存：

WITH ProductStock AS (
    -- 统计每个产品的初始库存数量
    SELECT ProdName, COUNT(*) AS AvailableQty
    FROM Warehouse
    GROUP BY ProdName
),
OrderedOrderItems AS (
    -- 给订单排序，确定处理优先级（按订单号升序）
    SELECT 
        OrderNo,
        RequiredProdName,
        DENSE_RANK() OVER (ORDER BY OrderNo) AS ProcessOrder
    FROM Orders
),
InventoryAllocation AS (
    -- 递归初始状态：未处理任何订单时的库存
    SELECT 
        CAST(NULL AS VARCHAR(10)) AS ProcessedOrder,
        0 AS CurrentProcessStep,
        ps.ProdName,
        ps.AvailableQty AS RemainingQty
    FROM ProductStock ps

    UNION ALL

    -- 递归处理每个订单
    SELECT
        CASE WHEN IsExecutable = 1 THEN ooi.OrderNo ELSE ia.ProcessedOrder END,
        ooi.ProcessOrder,
        ia.ProdName,
        -- 若订单可执行，扣除当前产品的用量；否则库存不变
        CASE 
            WHEN IsExecutable = 1 AND ia.ProdName = ooi.RequiredProdName THEN ia.RemainingQty - 1
            ELSE ia.RemainingQty
        END AS RemainingQty
    FROM InventoryAllocation ia
    JOIN OrderedOrderItems ooi ON ia.CurrentProcessStep + 1 = ooi.ProcessOrder
    -- 提前判断当前订单是否所有产品库存都满足需求
    CROSS JOIN (
        SELECT 
            CASE 
                WHEN COUNT(*) = SUM(CASE WHEN 
                    (SELECT RemainingQty FROM InventoryAllocation 
                     WHERE ProdName = o.RequiredProdName AND CurrentProcessStep = ooi.ProcessOrder - 1) >= 1 
                THEN 1 ELSE 0 END) 
                THEN 1 ELSE 0 
            END AS IsExecutable
        FROM OrderedOrderItems o
        WHERE o.OrderNo = ooi.OrderNo
        GROUP BY o.OrderNo
    ) ExecCheck
)
-- 提取所有可执行的订单并去重
SELECT DISTINCT ProcessedOrder AS ExecutableOrders
FROM InventoryAllocation
WHERE ProcessedOrder IS NOT NULL
ORDER BY ProcessedOrder;

工作原理

ProductStock：先统计每个产品的初始库存，以ProdName分组计数得到可用数量。
OrderedOrderItems：为每个订单按编号排序，生成处理顺序号，确保订单按顺序被验证。
InventoryAllocation：递归CTE从初始库存开始，每一步处理下一个订单：
- 先检查该订单所有需求产品的剩余库存是否都≥1；
- 若满足，标记订单为可执行，并扣除对应产品的库存；
- 若不满足，跳过该订单，库存保持不变。
最后通过去重筛选出所有可执行的订单，得到结果。

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