目录
在企业级数据处理中，BOM（物料清单）数据的处理一直是让开发者头疼的问题。特别是当客户要求将复杂的多层嵌套BOM结构展开成一层结构时，如何保证数据准确性、性能优化和业务逻辑的正确实现？
今天就来分享一个真实项目中的SQL Server BOM数据展开解决方案，这个主要是有同事需要用BOM做回冲，说是要某些叶子级物料，用CTE不仅解决了递归展开问题，还巧妙处理了客户的特殊业务需求。
🎯 问题分析：BOM数据展开的核心挑战
业务背景
我们面临的是一个典型的制造业BOM数据处理需求：
- 多层嵌套结构：BOM数据具有父子层级关系
- 数量累积计算：子级数量需要根据层级进行累积计算
- 特殊业务规则：客户要求在特定条件下停止展开
- 性能要求：大数据量下的高效处理
核心难点
- 递归查询复杂度：如何优雅地处理多层递归
- 数量计算精度：避免浮点数精度丢失
- 业务逻辑集成：将复杂的业务规则融入SQL逻辑
- 性能优化：确保大数据量下的查询效率
💡 解决方案：CTE递归查询的最佳实践
🚀 完整解决方案
SQL-- BOM数据一层展开解决方案
WITH ParentMaterials AS (
-- 第一步：获取所有顶级父物料
SELECT DISTINCT MATNR1, WERKS
FROM [sap_bom]
WHERE STUFE = 1
AND IsDelete = 0
AND MATNR1 IS NOT NULL
AND WERKS IS NOT NULL
),
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,
1 AS Level,
CAST(b.MENG AS DECIMAL(18,6)) AS AccumulatedQty
FROM [sap_bom] b
INNER JOIN ParentMaterials p ON b.MATNR1 = p.MATNR1 AND b.WERKS = p.WERKS
WHERE b.STUFE = 1 AND 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 AS DECIMAL(18,6))
FROM [sap_bom] b
INNER JOIN BOM_Expansion e ON b.MATNR1 = e.ChildMaterial AND b.WERKS = e.WERKS
WHERE b.STUFE = 1
AND (e.DUMPS = 'x' OR RIGHT(RTRIM(e.ChildMaterial), 1) = 'M' OR e.BESKZ = 'E')
AND b.IsDelete = 0
AND e.Level < 10 -- 防止无限递归
-- 🔥 关键业务逻辑：特定条件下停止展开
AND NOT EXISTS (
SELECT 1
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 IS NULL OR DUMPS != 'x')
AND RIGHT(RTRIM(ChildMaterial), 1) != 'M'
)
SELECT * FROM FinalResult;
🔧 核心技术点解析
1. CTE递归查询结构
SQLWITH RecursiveCTE AS (
-- 锚点查询（基础情况）
SELECT ... WHERE 基础条件
UNION ALL
-- 递归查询（递归情况）
SELECT ... FROM table INNER JOIN RecursiveCTE ON 递归条件
)
2. 精确的数量计算
SQL-- 使用DECIMAL类型确保精度
CAST(e.AccumulatedQty * b.MENG AS DECIMAL(18,6))
3. 递归终止条件
SQL-- 多重安全机制
AND e.Level < 10 -- 层级限制
AND NOT EXISTS (...) -- 业务规则限制
🛠️ 实战应用技巧
⚡ 性能优化建议
- 合适的索引策略
SQL-- 建议创建的复合索引
CREATE INDEX IX_sap_bom_expansion
ON [sap_bom] (MATNR1, WERKS, STUFE, IsDelete)
INCLUDE (IDNRK, MENG, DUMPS, BESKZ);
- 查询提示优化
SQL-- 对于大数据量，可以添加查询提示
SELECT * FROM FinalResult
OPTION (MAXRECURSION 20); -- 限制递归深度
🎯 常见坑点避免
1. 无限递归问题
SQL-- ❌ 危险：没有递归限制
WITH BadRecursion AS (
SELECT ...
UNION ALL
SELECT ... FROM BadRecursion
-- 缺少终止条件！
)
-- ✅ 安全：多重保护机制
WHERE e.Level < 10 AND 其他业务条件
2. 数据类型精度丢失
SQL-- ❌ 可能丢失精度
AccumulatedQty * b.MENG
-- ✅ 保证精度
CAST(AccumulatedQty * b.MENG AS DECIMAL(18,6))
3. 空值处理
SQL-- 关键字段的空值检查
WHERE MATNR1 IS NOT NULL
AND WERKS IS NOT NULL
📊 实际应用场景扩展
🔄 场景1：成本计算
SQL-- 在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：库存需求计算
SQL-- 根据生产计划计算原材料需求
WITH MaterialDemand AS (
SELECT fr.ChildMaterial,
SUM(fr.AccumulatedQty * pp.PlanQuantity) AS TotalDemand
FROM FinalResult fr
INNER JOIN ProductionPlan pp ON fr.ParentMaterial = pp.ProductCode
WHERE pp.PlanDate BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY fr.ChildMaterial
)
SELECT * FROM MaterialDemand
WHERE TotalDemand > 0;
🎉 总结与提升
这个BOM数据展开解决方案的三大核心优势：
- 🎯 精确性：通过DECIMAL类型和严格的业务逻辑确保数据准确性
- ⚡ 高效性：合理的CTE结构和索引策略保证查询性能
- 🔧 灵活性：模块化设计便于适应不同的业务需求
在实际项目中，这个方案不仅解决了复杂的BOM展开需求，还为后续的成本分析、库存管理等功能奠定了坚实基础。
💡 你在项目中遇到过哪些复杂的数据结构处理需求？
🚀 这个BOM展开方案是否给了你新的思路？
如果觉得这个解决方案对你有帮助，别忘了分享给更多需要的同行！让我们一起在SQL Server的技术道路上越走越远！
本文作者:技术老小子
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
目录