基于SQL递归CTE计算多层BOM产品价格的技术咨询

• 本人SQL水平一般，查过CTE递归查询展开BOM的相关资料，但还是解决不了问题。现有表结构包含DB_PARENT、DB_COMPONENT、DB_COEFF三个字段，需要计算DB_PARENT列中所有产品的价格，规则如下：
  • 产品由原材料或半成品构成，部分产品包含多个组成项，必须拆解到最底层的原材料，按DB_COEFF系数相乘计算成本；
  • 如果产品包含半成品，需要先算出半成品的价格，再累加进最终产品成本。
• 尝试过以下递归查询，但效果不理想：

with a as(
  SELECT [dbo].[ANAG_DBASE].DB_PARENT, [dbo].[ANAG_DBASE].DB_COMPONENT, 1 * [dbo].[ANAG_DBASE].DB_COEFF as price, 1 as level,
  convert(varchar(max), [dbo].[ANAG_DBASE].DB_COMPONENT) as path
  FROM [dbo].[ANAG_DBASE]
 
  UNION ALL
  SELECT [dbo].[ANAG_DBASE].DB_PARENT, [dbo].[ANAG_DBASE].DB_COMPONENT, 1 * [dbo].[ANAG_DBASE].DB_COEFF as price, a.level + 1 as level,
  a.path + '/' + [dbo].[ANAG_DBASE].DB_COMPONENT
  FROM a
  INNER JOIN [dbo].[ANAG_DBASE] ON a.DB_PARENT = [dbo].[ANAG_DBASE].DB_COMPONENT)
select DB_PARENT, sum(price), level from a
group by DB_PARENT, level

问题分析与修正方案 #

原递归查询的核心问题：

1. 初始查询直接取全表数据，没区分原材料和半成品，递归逻辑混乱；
2. 价格只取当前行的DB_COEFF，没有累积相乘半成品的系数链；
3. 按DB_PARENT+level分组，无法得到产品最终总成本。

修正后的递归CTE逻辑：

• 先锚定原材料（没有子组件的项）作为递归起点；
• 递归时逐层累积系数乘积，传递半成品的成本；
• 最终按产品编码汇总总成本。

修正代码示例：

WITH BOMRecursive AS (
    -- 锚点：筛选原材料（无下属组件的项）
    SELECT 
        DB_PARENT,
        DB_COMPONENT,
        DB_COEFF AS TotalCoeff,
        DB_COEFF AS Price, -- 此处假设原材料价格=自身系数，有单独价格表需替换
        1 AS Level,
        CAST(DB_COMPONENT AS VARCHAR(MAX)) AS Path
    FROM [dbo].[ANAG_DBASE]
    WHERE DB_COMPONENT NOT IN (SELECT DB_PARENT FROM [dbo].[ANAG_DBASE])

    UNION ALL

    -- 递归：向上计算半成品/成品的成本
    SELECT 
        parent.DB_PARENT,
        child.DB_COMPONENT,
        parent.DB_COEFF * child.TotalCoeff AS TotalCoeff,
        parent.DB_COEFF * child.Price AS Price,
        child.Level + 1 AS Level,
        child.Path + '/' + parent.DB_COMPONENT AS Path
    FROM [dbo].[ANAG_DBASE] parent
    INNER JOIN BOMRecursive child ON parent.DB_COMPONENT = child.DB_PARENT
)
-- 汇总每个产品的总成本
SELECT 
    DB_PARENT AS 产品编码,
    SUM(Price) AS 总成本
FROM BOMRecursive
GROUP BY DB_PARENT
ORDER BY DB_PARENT;

补充说明 #

• 如果有单独的原材料价格表，需在锚点成员中关联该表，替换Price字段的值；
• 递归过程中TotalCoeff用于记录从原材料到当前层级的系数乘积，确保成本传递准确；
• 最终分组直接按DB_PARENT汇总，得到每个产品的最终成本。

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