多托盘与生产批次的SQL递归成本追溯问题

核心思路 #

要解决多对多递归中的无限循环问题，核心是追踪已处理的托盘-生产批次组合，同时按生产批次的输入占比逐层向上分摊成本，最终汇总到原始托盘（Type='N'）。需明确每个批次的输入输出托盘关联关系及使用比例。

关键表结构假设 #

需确保存在以下核心表（可根据实际结构调整）：

• Pallets：存储所有托盘信息，字段包括PalletID（主键）、Type（'N'=原始，'P'=生产生成）、ProdOrd（仅'P'类型对应所属批次号）
• ProdOrdBOM：记录生产批次的输入输出关联，字段包括ProdOrd（批次号）、InputPalletID（输入托盘ID）、OutputPalletID（输出托盘ID）、QtyUsed（输入托盘在批次中的使用量）
• ProdOrdCosts：存储每个生产批次的总成本，字段包括ProdOrd、TotalMaterialCost、TotalLaborCost（可从Pallets表汇总输出托盘成本替代）

递归CTE实现（SQL示例） #

通过递归CTE逐层追溯上游托盘，同时记录处理路径避免循环：

WITH CostAllocation AS (
    -- 锚点成员：初始化批次直接输入托盘的分摊成本
    SELECT 
        po.ProdOrd,
        pi.InputPalletID AS PalletID,
        p.Type,
        -- 计算输入托盘在当前批次的分摊比例
        pi.QtyUsed / SUM(pi.QtyUsed) OVER (PARTITION BY po.ProdOrd) AS AllocationRatio,
        -- 按比例分摊批次成本到输入托盘
        po.TotalMaterialCost * (pi.QtyUsed / SUM(pi.QtyUsed) OVER (PARTITION BY po.ProdOrd)) AS AllocatedMaterialCost,
        po.TotalLaborCost * (pi.QtyUsed / SUM(pi.QtyUsed) OVER (PARTITION BY po.ProdOrd)) AS AllocatedLaborCost,
        -- 记录已处理的批次-托盘组合，用字符串拼接标记路径
        CONCAT(po.ProdOrd, '|', pi.InputPalletID) AS ProcessedPath
    FROM ProdOrdCosts po
    JOIN ProdOrdBOM pi ON po.ProdOrd = pi.ProdOrd
    JOIN Pallets p ON pi.InputPalletID = p.PalletID

    UNION ALL

    -- 递归成员：追溯生产生成托盘的上游输入，继续分摊成本
    SELECT 
        parent_po.ProdOrd,
        parent_pi.InputPalletID AS PalletID,
        parent_p.Type,
        -- 累积分摊比例：当前比例 × 上游批次的分摊比例
        ca.AllocationRatio * (parent_pi.QtyUsed / SUM(parent_pi.QtyUsed) OVER (PARTITION BY parent_po.ProdOrd)) AS AllocationRatio,
        -- 累积分摊成本：当前已分摊成本 × 上游批次的分摊比例
        ca.AllocatedMaterialCost * (parent_pi.QtyUsed / SUM(parent_pi.QtyUsed) OVER (PARTITION BY parent_po.ProdOrd)) AS AllocatedMaterialCost,
        ca.AllocatedLaborCost * (parent_pi.QtyUsed / SUM(parent_pi.QtyUsed) OVER (PARTITION BY parent_po.ProdOrd)) AS AllocatedLaborCost,
        -- 更新处理路径，避免重复处理同一组合
        CONCAT(ca.ProcessedPath, '|', parent_po.ProdOrd, '|', parent_pi.InputPalletID) AS ProcessedPath
    FROM CostAllocation ca
    JOIN Pallets child_p ON ca.PalletID = child_p.PalletID
    -- 仅处理生产生成的托盘，原始托盘无需继续追溯
    WHERE child_p.Type = 'P'
    -- 关联当前托盘所属批次的上游输入托盘
    JOIN ProdOrdBOM parent_pi ON child_p.ProdOrd = parent_pi.ProdOrd 
        AND parent_pi.OutputPalletID = child_p.PalletID
    JOIN ProdOrdCosts parent_po ON parent_pi.ProdOrd = parent_po.ProdOrd
    JOIN Pallets parent_p ON parent_pi.InputPalletID = parent_p.PalletID
    -- 核心防循环逻辑：检查当前批次-托盘组合是否已处理过
    WHERE CHARINDEX(CONCAT(parent_po.ProdOrd, '|', parent_pi.InputPalletID), ca.ProcessedPath) = 0
)
-- 汇总原始托盘的所有分摊成本
SELECT 
    PalletID,
    SUM(AllocatedMaterialCost) AS TotalAllocatedMaterialCost,
    SUM(AllocatedLaborCost) AS TotalAllocatedLaborCost
FROM CostAllocation
WHERE Type = 'N'
GROUP BY PalletID
ORDER BY PalletID;

优化要点 #

1. 防循环逻辑优化：如果数据库支持数组/JSON类型（如PostgreSQL的ARRAY、SQL Server的JSON），可替换字符串拼接为更高效的集合类型检查，提升性能。
2. 索引优化：给Pallets.Type、ProdOrdBOM.ProdOrd、ProdOrdBOM.InputPalletID、ProdOrdBOM.OutputPalletID建立索引，加速递归查询。
3. 递归深度控制：添加OPTION (MAXRECURSION N)（N为合理递归深度，如100），避免无限制递归消耗资源。
4. 分批处理：针对数千个原始托盘，可按批次或托盘范围分批执行查询，降低单次查询压力。

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