在企业级数据处理中，BOM（物料清单）数据的处理一直是让开发者头疼的问题。特别是当客户要求将复杂的多层嵌套BOM结构展开成一层结构时，如何保证数据准确性、性能优化和业务逻辑的正确实现？

今天就来分享一个真实项目中的SQL Server BOM数据展开解决方案，这个主要是有同事需要用BOM做回冲，说是要某些叶子级物料，用CTE不仅解决了递归展开问题，还巧妙处理了客户的特殊业务需求。

🎯 问题分析：BOM数据展开的核心挑战

业务背景

我们面临的是一个典型的制造业BOM数据处理需求：

• 多层嵌套结构
  BOM数据具有父子层级关系
• 数量累积计算
  子级数量需要根据层级进行累积计算
• 特殊业务规则
  客户要求在特定条件下停止展开
• 性能要求
  大数据量下的高效处理

核心难点

1. 递归查询复杂度
   如何优雅地处理多层递归
2. 数量计算精度
   避免浮点数精度丢失
3. 业务逻辑集成
   将复杂的业务规则融入SQL逻辑
4. 性能优化
   确保大数据量下的查询效率

💡 解决方案：CTE递归查询的最佳实践

🚀 完整解决方案

-- BOM数据一层展开解决方案
WITH ParentMaterials AS (
    -- 第一步：获取所有顶级父物料
    SELECTDISTINCT MATNR1, WERKS
    FROM [sap_bom]
    WHERE STUFE = 1
      AND IsDelete = 0
      AND MATNR1 ISNOTNULL
      AND WERKS ISNOTNULL
),
BOM_Expansion AS (
    -- 第二步：递归展开BOM结构

    -- 递归基础：获取第一层子物料
    SELECT
        b.WERKS, 
        b.MATNR1 AS ParentMaterial, 
        b.MAKTX1 AS ParentDesc,
        b.IDNRK AS ChildMaterial, 
        b.MAKTX AS ChildDesc, 
        b.MENG AS Quantity,
        b.DUMPS, b.STUFE, b.BESKZ, 
        1ASLevel,
        CAST(b.MENG ASDECIMAL(18,6)) AS AccumulatedQty
    FROM [sap_bom] b
    INNERJOIN ParentMaterials p ON b.MATNR1 = p.MATNR1 AND b.WERKS = p.WERKS
    WHERE b.STUFE = 1AND b.IsDelete = 0

    UNION ALL

    -- 递归部分：展开下一层
    SELECT
        b.WERKS, 
        e.ParentMaterial, 
        e.ParentDesc, 
        b.IDNRK, 
        b.MAKTX, 
        b.MENG,
        b.DUMPS, b.STUFE, b.BESKZ, 
        e.Level + 1,
        CAST(e.AccumulatedQty * b.MENG ASDECIMAL(18,6))
    FROM [sap_bom] b
    INNERJOIN BOM_Expansion e ON b.MATNR1 = e.ChildMaterial AND b.WERKS = e.WERKS
    WHERE b.STUFE = 1
      AND (e.DUMPS = 'x'ORRIGHT(RTRIM(e.ChildMaterial), 1) = 'M'OR e.BESKZ = 'E')
      AND b.IsDelete = 0
      AND e.Level < 10-- 防止无限递归
      -- 🔥 关键业务逻辑：特定条件下停止展开
      ANDNOTEXISTS (
          SELECT1
          FROM [WebAppDb].[dbo].[as_tm_part] sp 
          WHERE sp.part_no = e.ChildMaterial 
            AND sp.site_code = e.WERKS
      )
),
FinalResult AS (
    -- 第三步：过滤最终结果
    SELECT WERKS, ParentMaterial, ParentDesc, ChildMaterial, ChildDesc,
           Quantity, AccumulatedQty, Level, STUFE
    FROM BOM_Expansion
    WHERE (DUMPS ISNULLOR DUMPS != 'x')
      ANDRIGHT(RTRIM(ChildMaterial), 1) != 'M'
)
SELECT * FROM FinalResult;

🔧 核心技术点解析

1. CTE递归查询结构

WITH RecursiveCTE AS (
    -- 锚点查询（基础情况）
    SELECT ... WHERE 基础条件

    UNION ALL

    -- 递归查询（递归情况）
    SELECT ... FROM table INNER JOIN RecursiveCTE ON 递归条件
)

2. 精确的数量计算

-- 使用DECIMAL类型确保精度
CAST(e.AccumulatedQty * b.MENG AS DECIMAL(18,6))

3. 递归终止条件

-- 多重安全机制
AND e.Level < 10  -- 层级限制
AND NOT EXISTS (...)  -- 业务规则限制

🛠️ 实战应用技巧

⚡ 性能优化建议

1. 合适的索引策略

-- 建议创建的复合索引
CREATE INDEX IX_sap_bom_expansion 
ON [sap_bom] (MATNR1, WERKS, STUFE, IsDelete) 
INCLUDE (IDNRK, MENG, DUMPS, BESKZ);

2. 查询提示优化

-- 对于大数据量，可以添加查询提示
SELECT * FROM FinalResult
OPTION (MAXRECURSION 20);  -- 限制递归深度

🎯 常见坑点避免

1. 无限递归问题

-- ❌ 危险：没有递归限制
WITH BadRecursion AS (
    SELECT ... 
    UNION ALL
    SELECT ... FROM BadRecursion
    -- 缺少终止条件！
)

-- ✅ 安全：多重保护机制
WHERE e.Level < 10 AND 其他业务条件

2. 数据类型精度丢失

-- ❌ 可能丢失精度
AccumulatedQty * b.MENG

-- ✅ 保证精度
CAST(AccumulatedQty * b.MENG AS DECIMAL(18,6))

3. 空值处理

-- 关键字段的空值检查
WHERE MATNR1 IS NOT NULL
  AND WERKS IS NOT NULL

📊 实际应用场景扩展

🔄 场景1：成本计算

-- 在BOM展开基础上进行成本汇总
WITH BOMCost AS (
    SELECT ParentMaterial, ChildMaterial, AccumulatedQty,
           AccumulatedQty * UnitCost AS TotalCost
    FROM FinalResult fr
    INNER JOIN MaterialCost mc ON fr.ChildMaterial = mc.MaterialNo
)
SELECT ParentMaterial, SUM(TotalCost) AS TotalMaterialCost
FROM BOMCost
GROUP BY ParentMaterial;

📦 场景2：库存需求计算

-- 根据生产计划计算原材料需求
WITH MaterialDemand AS (
    SELECT fr.ChildMaterial, 
           SUM(fr.AccumulatedQty * pp.PlanQuantity) AS TotalDemand
    FROM FinalResult fr
    INNERJOIN ProductionPlan pp ON fr.ParentMaterial = pp.ProductCode
    WHERE pp.PlanDate BETWEEN'2025-01-01'AND'2025-12-31'
    GROUPBY fr.ChildMaterial
)
SELECT * FROM MaterialDemand
WHERE TotalDemand > 0;

🎉 总结与提升

这个BOM数据展开解决方案的三大核心优势：

1. 🎯 精确性
   通过DECIMAL类型和严格的业务逻辑确保数据准确性
2. ⚡ 高效性
   合理的CTE结构和索引策略保证查询性能
3. 🔧 灵活性
   模块化设计便于适应不同的业务需求

在实际项目中，这个方案不仅解决了复杂的BOM展开需求，还为后续的成本分析、库存管理等功能奠定了坚实基础。

💡 你在项目中遇到过哪些复杂的数据结构处理需求？

🚀 这个BOM展开方案是否给了你新的思路？

阅读原文：原文链接