如何使用递归CTE计算物料需求的最早日期？

数据定义 #

先创建并填充临时表：

-- 库存表
CREATE TABLE #Inventory (
    Material VARCHAR(20),
    QtyOnHand INT
);
INSERT INTO #Inventory VALUES ('Matl_A', 50);

-- 库存补单表（待填充DueDate字段）
CREATE TABLE #ToStockOrders (
    Job VARCHAR(20),
    ItemProduced VARCHAR(20),
    StockQty INT,
    DueDate DATE NULL
);
INSERT INTO #ToStockOrders VALUES 
('Job_1', 'Matl_A', 25, NULL),
('Job_2', 'Matl_A', 50, NULL),
('Job_3', 'Matl_A', 25, NULL);

-- 客户需求表
CREATE TABLE #CustomerDemand (
    DueDate DATE,
    Job VARCHAR(20),
    MatlQty INT,
    BillOfMaterials VARCHAR(20)
);
INSERT INTO #CustomerDemand VALUES 
('2023-01-01', 'Job_A', 100, 'Matl_A'),
('2023-02-01', 'Job_B', 100, 'Matl_A'),
('2023-03-01', 'Job_C', 100, 'Matl_A');

核心计算逻辑 #

按客户需求的日期顺序分配补单产能，优先满足最早的需求：

• 处理Job_A（2023-01-01）：需求100个Matl_A，现有库存50，缺口50个
• 分配Job_1的25个至Job_A，设置Job_1.DueDate = '2023-01-01'，缺口剩余25个
• 分配Job_2的25个至Job_A，设置Job_2.DueDate = '2023-01-01'，Job_A需求满足，Job_2剩余25个产能转为可用库存
• 处理Job_B（2023-02-01）：需求100个Matl_A，用Job_2剩余的25个后，缺口75个
• 分配Job_3的25个至Job_B，设置Job_3.DueDate = '2023-02-01'，所有补单的DueDate填充完成

递归CTE实现代码 #

递归CTE会跟踪当前可用库存、剩余需求以及已处理的补单，逐步完成分配并设置DueDate：

WITH OrderedDemand AS (
    -- 按日期排序客户需求，生成需求序号
    SELECT 
        BillOfMaterials,
        DueDate,
        MatlQty,
        ROW_NUMBER() OVER (PARTITION BY BillOfMaterials ORDER BY DueDate) AS DemandSeq
    FROM #CustomerDemand
),
OrderedStockJobs AS (
    -- 给补单按原始顺序生成序号
    SELECT 
        Job,
        ItemProduced,
        StockQty,
        ROW_NUMBER() OVER (PARTITION BY ItemProduced ORDER BY (SELECT NULL)) AS JobSeq
    FROM #ToStockOrders
),
RecursiveAllocation AS (
    -- 递归起始：初始库存+第一个需求+第一个补单
    SELECT
        od.BillOfMaterials,
        od.DueDate AS CurrentDemandDate,
        od.MatlQty AS RemainingDemand,
        osj.Job,
        osj.ItemProduced,
        osj.StockQty,
        osj.JobSeq,
        inv.QtyOnHand AS AvailableStock,
        -- 计算当前补单需分配的数量
        CASE 
            WHEN inv.QtyOnHand >= od.MatlQty THEN 0
            ELSE IIF((od.MatlQty - inv.QtyOnHand) <= osj.StockQty, (od.MatlQty - inv.QtyOnHand), osj.StockQty)
        END AS AllocatedQty,
        -- 确定当前补单的DueDate
        CASE WHEN inv.QtyOnHand >= od.MatlQty THEN NULL ELSE od.DueDate END AS JobDueDate
    FROM OrderedDemand od
    JOIN #Inventory inv ON od.BillOfMaterials = inv.Material
    JOIN OrderedStockJobs osj ON od.BillOfMaterials = osj.ItemProduced
    WHERE od.DemandSeq = 1 AND osj.JobSeq = 1

    UNION ALL

    -- 递归迭代：处理剩余需求或切换到下一个需求/补单
    SELECT
        ra.BillOfMaterials,
        -- 若当前需求已满足，切换到下一个需求的日期
        CASE WHEN ra.RemainingDemand - ra.AllocatedQty <= 0 THEN od_next.DueDate ELSE ra.CurrentDemandDate END AS CurrentDemandDate,
        -- 更新剩余需求
        CASE 
            WHEN ra.RemainingDemand - ra.AllocatedQty <= 0 THEN od_next.MatlQty
            ELSE ra.RemainingDemand - ra.AllocatedQty
        END AS RemainingDemand,
        osj_next.Job,
        osj_next.ItemProduced,
        osj_next.StockQty,
        osj_next.JobSeq,
        -- 更新可用库存：现有库存 + 当前补单剩余产能
        ra.AvailableStock + ra.StockQty - ra.AllocatedQty AS AvailableStock,
        -- 计算下一个补单的分配数量
        CASE 
            WHEN ra.RemainingDemand - ra.AllocatedQty <= 0 THEN
                IIF((od_next.MatlQty - (ra.AvailableStock + ra.StockQty - ra.AllocatedQty)) <= 0, 0,
                    IIF((od_next.MatlQty - (ra.AvailableStock + ra.StockQty - ra.AllocatedQty)) <= osj_next.StockQty,
                        (od_next.MatlQty - (ra.AvailableStock + ra.StockQty - ra.AllocatedQty)), osj_next.StockQty))
            ELSE
                IIF(ra.RemainingDemand - ra.AllocatedQty <= osj_next.StockQty, ra.RemainingDemand - ra.AllocatedQty, osj_next.StockQty)
        END AS AllocatedQty,
        -- 设置下一个补单的DueDate
        CASE 
            WHEN ra.RemainingDemand - ra.AllocatedQty <= 0 THEN
                IIF((od_next.MatlQty - (ra.AvailableStock + ra.StockQty - ra.AllocatedQty)) <= 0, NULL, od_next.DueDate)
            ELSE ra.CurrentDemandDate
        END AS JobDueDate
    FROM RecursiveAllocation ra
    JOIN OrderedStockJobs osj_next ON ra.ItemProduced = osj_next.ItemProduced AND osj_next.JobSeq = ra.JobSeq + 1
    -- 关联下一个需求（如果当前需求已满足）
    LEFT JOIN OrderedDemand od_next ON ra.BillOfMaterials = od_next.BillOfMaterials 
        AND od_next.DemandSeq = (
            SELECT MIN(DemandSeq) FROM OrderedDemand 
            WHERE BillOfMaterials = ra.BillOfMaterials AND DemandSeq > (
                SELECT DemandSeq FROM OrderedDemand 
                WHERE BillOfMaterials = ra.BillOfMaterials AND DueDate = ra.CurrentDemandDate
            )
        )
    -- 终止条件：所有补单处理完成
    WHERE ra.JobSeq < (SELECT MAX(JobSeq) FROM OrderedStockJobs WHERE ItemProduced = ra.ItemProduced)
)
-- 更新补单表的DueDate字段
UPDATE tso
SET DueDate = ra.JobDueDate
FROM #ToStockOrders tso
JOIN RecursiveAllocation ra ON tso.Job = ra.Job;

-- 查询最终结果
SELECT * FROM #ToStockOrders;

-- 清理临时表
DROP TABLE #Inventory;
DROP TABLE #ToStockOrders;
DROP TABLE #CustomerDemand;

最终结果 #

执行代码后，#ToStockOrders的输出结果为：

完全符合需求中的计算逻辑。

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