先看代码：with cte as
(
select
0 as BOM层次,t1.fid as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as 项次组合
,cast(CAST(t1.fid AS nvarchar)+'-'+CAST(1000000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM内码和项次组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,case when FMATERIALTYPE = 1 then '标准件'
when FMATERIALTYPE = 2 then '返还件'
when FMATERIALTYPE = 3 then '替代件'
else '未知类型' end as 子项类型
,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID,CAST((t3.FNUMERATOR/t3.FDENOMINATOR) AS decimal(20,10)) 标准用量
,CAST(((t3.FNUMERATOR/t3.FDENOMINATOR)+((t3.FNUMERATOR/t3.FDENOMINATOR)*CASE WHEN t3.FSCRAPRATE>0 THEN t3.FSCRAPRATE/100 ELSE 0 END)) AS decimal(20,10)) 实际用量
-----CAST(t3.FNUMERATOR/t3.FDENOMINATOR AS decimal(20,10)) 用量
from dbo.T_ENG_BOM t1
join T_BD_MATERIAL fxwl --用父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = 'A' --只取未禁用状态的BOM
join T_BD_MATERIAL_L fxwl_L --用父项关联物料多语言表
on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
join T_BD_MATERIALPRODUCE fxwl_P
on fxwl_P.FMATERIALID = fxwl.FMATERIALID
join T_ENG_BOMCHILD t3
on t1.fid = t3.FID
join T_BD_MATERIAL zxwl --用子项关联物料表
on zxwl.FMATERIALID = t3.FMATERIALID
join T_BD_MATERIAL_L zxwl_L --用子项关联物料多语言表
on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
where 1=1
and fxwl_P.FISMAINPRD = 1 --物料-生产页签的'可为主产品'属性FISMAINPRD,等于1就意味着可以建立BOM
and fxwl.FNUMBER='PCNTE02-0895'
union all
select
p.BOM层次+1 as BOM层次,P.最顶级BOM内码 as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,cast(p.项次组合+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as 项次组合
,cast(p.BOM内码和项次组合 +'.'+ ( CAST(t1.FID AS nvarchar) + '-' +CAST(10000+t3.FREPLACEGROUP AS nvarchar) ) as nvarchar(max)) as BOM内码组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,case when FMATERIALTYPE = 1 then '标准件'
when FMATERIALTYPE = 2 then '返还件'
when FMATERIALTYPE = 3 then '替代件'
else '未知类型' end as 子项类型
,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID,CAST((t3.FNUMERATOR/t3.FDENOMINATOR)*p.标准用量 AS decimal(20,10)) 标准用量,
CAST(((t3.FNUMERATOR/t3.FDENOMINATOR)+((t3.FNUMERATOR/t3.FDENOMINATOR)*CASE WHEN t3.FSCRAPRATE>0 THEN t3.FSCRAPRATE/100 ELSE 0 END))*p.实际用量 AS decimal(20,10)) 实际用量
from cte P --调用递归CTE本身
join dbo.T_ENG_BOM t1
on t1.FMATERIALID = p.子项物料内码
join T_BD_MATERIAL fxwl --父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = 'A'
join T_BD_MATERIAL_L fxwl_L --父项关联物料多语言表
on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
join T_ENG_BOMCHILD t3
on
