用友U9 BOM全阶展开SQL代码深度解析：从递归逻辑到业务映射
在ERP系统实施过程中，物料清单(BOM)的展开查询是生产制造模块的核心功能之一。用友U9作为国内领先的ERP解决方案，其BOM展开逻辑采用了递归CTE(Common Table Expression)技术实现多层级物料关系的遍历。本文将深入剖析这段看似复杂但设计精巧的SQL代码，帮助开发者掌握其技术实现原理和业务映射关系。
1. BOM数据结构与递归CTE基础
1.1 U9 BOM核心表结构
用友U9的BOM数据主要存储在三个关键表中：
- CBO_BOMMaster：存储BOM主表信息，包含BOM版本、生效日期等基础属性
- CBO_BOMComponent：存储BOM组件明细，记录子件与母件的用量关系
- CBO_ItemMaster：存储物料主数据，包含料品编码、名称、规格等基础信息
这三个表通过外键关联，构成了U9 BOM数据模型的基础框架。理解它们之间的关系是解析BOM展开逻辑的前提。
1.2 递归CTE的工作原理
递归CTE是SQL中处理层级数据的强大工具，其基本结构包含：
WITH RECURSIVE cte_name AS ( -- 基础查询（非递归部分） SELECT ... FROM ... UNION ALL -- 递归查询部分 SELECT ... FROM cte_name JOIN ... ) SELECT * FROM cte_name;递归CTE的执行过程分为三个阶段：
- 执行基础查询，生成初始结果集
- 将上一步结果作为输入，执行递归部分查询
- 重复步骤2，直到返回空集为止
在BOM展开场景中，这种特性非常适合处理"父项-子项"的无限级联关系。
2. 代码模块逐层解析
2.1 基础CTE：bomComponent
这段代码首先定义了bomComponentCTE，用于获取BOM组件的基本信息：
with bomComponent (FbomMaster,itemCode,itemName,itemSpecs,用量,母件底数,料品的形态属性,发料方式) as ( select c3.BOMMaster FbomMaster, c4.Code itemCode, c4.Name itemName, c4.SPECS itemSpecs, c3.UsageQty 用量, c3.ParentQty 母件底数, (case when c4.ItemFormAttribute=0 then '模型' when c4.ItemFormAttribute=1 then '按订单拣货' -- 其他形态属性映射... end) 料品的形态属性, (case when c3.IssueStyle=0 then '推式' when c3.IssueStyle=1 then '工序倒冲' -- 其他发料方式映射... end) 发料方式 from CBO_BOMComponent c3 left join CBO_ItemMaster c4 on c3.ItemMaster = c4.ID )关键点解析：
- 通过
CBO_BOMComponent和CBO_ItemMaster的关联获取组件详细信息 - 使用CASE语句将数字枚举值转换为业务可读的文本
用量和母件底数字段决定了物料的标准消耗量
2.2 中间CTE：FbomComponent和fullBom
接下来定义了两个辅助CTE来构建完整BOM结构：
,FbomComponent (bomMaster,FitemCode, FitemName,FitemSpecs) as ( select m1.ID bomMaster, c2.Code FitemCode, c2.Name FitemName, c2.SPECS FitemSpecs from CBO_BOMMaster m1 left join CBO_ItemMaster c2 on m1.ItemMaster = c2.ID ) ,fullBom (bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 ) as ( select f.bomMaster bomMaster, f.FitemCode FitemCode, f.FitemName FitemName, f.FitemSpecs FitemSpecs, c.FbomMaster FbomMaster, c.itemCode itemCode, c.itemName itemName, c.itemSpecs itemSpecs, c.用量 用量, c.母件底数 母件底数, c.料品的形态属性 料品的形态属性, c.发料方式 发料方式 from FbomComponent f left join bomComponent c on c.FbomMaster = f.bomMaster )这两个CTE的作用：
FbomComponent：获取BOM母件的基本信息fullBom：将母件信息与子件信息关联，形成完整的BOM结构
2.3 递归CTE：tree_teset
核心的递归逻辑在tree_tesetCTE中实现：
, tree_teset ( bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 ) as ( -- 基础查询：获取完整的BOM结构 select bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 from fullBom --where FitemCode ='这里添加单个物料料号' --查询单个BOM union all -- 递归查询：通过物料编码关联实现层级展开 select t1.bomMaster,t1.FitemCode, t1.FitemName, t1.FitemSpecs, t1.FbomMaster , t1.itemCode, t1.itemName, t1.itemSpecs, t1.用量, t1.母件底数, t1.料品的形态属性, t1.发料方式 from fullBom t1 join tree_teset t2 on t1.FitemCode = t2.itemCode ) select * from tree_teset递归逻辑的关键在于：
- 基础查询获取初始BOM结构
- 递归部分通过
t1.FitemCode = t2.itemCode条件实现层级关联 - 每次递归都会将子件作为新的父件继续向下展开
3. 业务逻辑深度剖析
3.1 料品形态属性的业务含义
代码中对ItemFormAttribute字段的映射揭示了U9丰富的物料类型体系：
| 数值 | 类型名称 | 业务含义 |
|---|---|---|
| 0 | 模型 | 产品原型或模板 |
| 1 | 按订单拣货 | 根据客户订单直接拣货 |
| 2 | 按订单装配 | 需要根据订单进行装配 |
| 4 | 委外加工件 | 需要外协加工的物料 |
| 6 | 虚拟 | 不存在实物，仅用于逻辑计算 |
| 10 | 制造件 | 需要内部生产的物料 |
理解这些类型对于正确配置BOM至关重要。例如，虚拟件常用于：
- 简化BOM结构
- 处理通用组件
- 实现逻辑分组
3.2 发料方式的生产控制
IssueStyle字段决定了物料如何发放到生产现场：
(case when c3.IssueStyle=0 then '推式' when c3.IssueStyle=1 then '工序倒冲' when c3.IssueStyle=2 then '完工倒冲' when c3.IssueStyle=3 then '开工倒冲' when c3.IssueStyle=4 then '不发料' end) 发料方式不同发料方式的适用场景：
- 推式发料：传统方式，提前将物料发到工位
- 工序倒冲：完成特定工序后自动扣减库存
- 完工倒冲：产品完工后一次性扣减所有组件
- 不发料：不进行库存扣减，适用于虚拟件等
3.3 用量计算与母件底数
BOM展开中的关键计算字段：
c3.UsageQty 用量, -- 单件用量 c3.ParentQty 母件底数 -- 母件基准数量实际消耗量计算公式：
实际消耗 = 用量 × (母件需求数量 / 母件底数)例如：
- 母件A的底数为10
- 子件B的用量为2
- 需要生产母件A数量为30
- 则子件B的实际需求 = 2 × (30/10) = 6
4. 高级应用与性能优化
4.1 单物料查询的实现
代码中注释掉的过滤条件可用于查询特定物料的BOM结构：
--where FitemCode ='这里添加单个物料料号' --查询单个BOM实际应用时可以动态传入参数，例如：
DECLARE @ItemCode VARCHAR(50) = 'A1001'; WITH bomComponent AS (...) SELECT * FROM tree_teset WHERE FitemCode = @ItemCode;4.2 递归深度控制
无限递归可能导致性能问题，可通过OPTION子句限制最大递归深度：
SELECT * FROM tree_teset OPTION (MAXRECURSION 100);4.3 物料层级标记
在实际应用中，通常需要显示物料在BOM中的层级关系。可以修改递归CTE添加层级计数：
, tree_teset (..., Level) as ( -- 基础查询 select ..., 0 as Level from fullBom union all -- 递归查询 select ..., t2.Level + 1 from fullBom t1 join tree_teset t2 on t1.FitemCode = t2.itemCode )4.4 用量累计计算
对于成本核算等场景，可能需要计算顶层到当前物料的累计用量：
, tree_teset (..., CumulativeUsage) as ( -- 基础查询 select ..., 用量 as CumulativeUsage from fullBom union all -- 递归查询 select ..., t2.CumulativeUsage * t1.用量 from fullBom t1 join tree_teset t2 on t1.FitemCode = t2.itemCode )5. 实际项目中的经验分享
在多个U9实施项目中，BOM展开查询常遇到以下典型问题：
性能瓶颈：对于深层次、多分支的BOM结构，递归查询可能较慢。解决方案包括：
- 添加适当的索引（如ItemCode、BOMMaster字段）
- 考虑使用临时表存储中间结果
- 对非常复杂的BOM采用分批处理
虚拟件处理：虚拟件不参与实际库存扣减，但会影响MRP计算。确保：
- 正确标记虚拟件的ItemFormAttribute
- 在MRP参数中配置虚拟件的处理规则
替代料管理：标准BOM展开不包含替代料信息，需要额外关联替代料表：
LEFT JOIN CBO_BOMSubstitute sub ON c3.ID = sub.BOMComponent版本控制：生产环境中通常需要指定BOM版本：
WHERE m1.Version = '2023-01'有效性检查：增加日期有效性过滤确保获取正确的BOM版本：
WHERE GETDATE() BETWEEN m1.EffectiveDate AND m1.ExpiryDate
对于需要频繁执行BOM展开的场景，建议将这段代码封装为存储过程，添加必要的参数控制和性能优化措施。在二次开发报表时，可以基于此基础查询进一步关联成本、库存等信息，构建完整的物料分析解决方案。
