如何在递归查询的递归项中避免使用聚合函数SUM

问题背景 #

需计算多级物料的需求数量：3种成品ABC1、ABC2、ABC3各售出10件，成品均由ABC制成，ABC由AB制成，AB由A制成。最终要输出包含物料编码、层级、销量/需求传导量、现有库存、需求数量的结果。现有三张表：Item（物料基础信息，含库存字段item_onhand）、BOM（物料清单，关联成品/半成品与原料）、Sales（销售数据）。因顶层向下查询性能过慢，必须从Sales表出发向上递归遍历物料树，原递归查询中SUM函数使用逻辑错误，需重新设计方案，约束：从Sales启动、支持任意层级、兼容多级BOM、顶层为单一物料。

原代码问题分析 #

原递归CTE存在核心缺陷：

• 未考虑BOM用量（即一个父物料需要多少子物料），需求传导逻辑缺失
• 未传递层级信息，无法区分物料所处的层级
• SUM函数使用时机错误，未按物料分组聚合就直接求和，导致数据重复计算

修正后的SQL实现 #

WITH RECURSIVE sales_req AS (
    -- 锚点成员：处理成品层，计算初始需求
    SELECT 
        s.sales_item_id AS item_id,
        SUM(s.sale_qty) AS sales_sold,
        i.item_onhand AS onhand_qty,
        -- 成品需求：销量减库存，负数取0（无需求）
        GREATEST(SUM(s.sale_qty) - i.item_onhand, 0) AS req_qty,
        0 AS level
    FROM sales s
    JOIN item i ON s.sales_item_id = i.item_id
    GROUP BY s.sales_item_id, i.item_onhand

    UNION ALL

    -- 递归成员：向上遍历BOM，计算子物料需求
    SELECT 
        b.bom_material_id AS item_id,
        -- 子物料传导量 = 父物料需求 × BOM用量（假设BOM表含bom_qty字段）
        SUM(sr.req_qty * b.bom_qty) AS sales_sold,
        i.item_onhand AS onhand_qty,
        -- 子物料实际需求：传导量减库存，负数取0
        GREATEST(SUM(sr.req_qty * b.bom_qty) - i.item_onhand, 0) AS req_qty,
        sr.level + 1 AS level
    FROM bom b
    JOIN sales_req sr ON b.bom_product_id = sr.item_id
    JOIN item i ON b.bom_material_id = i.item_id
    -- 仅父物料有实际需求时才递归，减少无效计算
    WHERE sr.req_qty > 0
    GROUP BY b.bom_material_id, i.item_onhand, sr.level + 1
)
-- 输出最终结果，按层级、物料编码排序
SELECT 
    item_id AS 物料编码,
    level AS 层级,
    sales_sold AS 销量/需求传导量,
    onhand_qty AS 现有库存,
    req_qty AS 需求数量
FROM sales_req
ORDER BY level, item_id;

关键逻辑说明 #

• 锚点成员：先聚合销售数据，得到每个成品的总销量，直接计算成品的净需求（销量减库存，不足则需求为0），层级设为0（成品层）。
• 递归成员：
  • 关联BOM表找到父物料对应的子物料
  • 按BOM用量传导父物料的需求，得到子物料的总需求传导量
  • 计算子物料的净需求：传导量减去自身库存，负数值取0（无需额外备货）
  • 层级自增1，明确物料所处的层级
• 聚合控制：在锚点和递归成员中分别按物料分组聚合，确保每个物料的数值是汇总后的结果，避免SUM函数误用导致的重复计算

适配调整 #

若你的BOM表没有bom_qty字段（默认1个父项对应1个物料），可将代码中的sr.req_qty * b.bom_qty替换为sr.req_qty。

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