SQL查询所有物料清单的最高层级

解决此问题的基本思路如下：

（1）把物料清单中的最高层物料（基本是成品物料）查询出来，作为一个待查列表，存到游标中；

（2）按（1）的结果列表全部循环查一遍子项，得出每一个成品物料的最大BOM层级；

（3）循环查询中，本次BOM的最大层级和上个物料最大层级做比较，保存两者中的值大者；

（4）直到循环完成，最终得出所有BOM中的最高层级。

具体SQL如下：

declare @fatherID int

declare @level int=0

declare @maxLevel int=0

declare @bomNumber varchar(50)=''

declare @maxBomNumber varchar(50)=''

declare  wlCursor cursor  for

select distinct  fmaterialid from  T_ENG_BOM 

where fmaterialid not in (select fmaterialid from T_ENG_BOMCHILD )

open  wlCursor

fetch next from wlCursor into @fatherID

while (@@FETCH_STATUS=0)

begin

;with bom as

( select 0 as BOM层次,t1.fid as 最顶级BOM内码,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,fxwl_L.FSPECIFICATION as 父项规格型号,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称,zxwl_L.FSPECIFICATION as 子项规格型号

from dbo.T_ENG_BOM t1

    join (select max(FNUMBER ) FNUMBER, FMATERIALID from T_ENG_BOM group by FNUMBER, FMATERIALID) tbom on tbom.FMATERIALID=t1.FMATERIALID 

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

join T_BD_MATERIALBASE fxjb --父项物料基本属性

    on fxjb.FMATERIALID=fxwl.FMATERIALID

        join T_BD_MATERIALBASE zxjb --子项物料基本属性

    on zxjb.FMATERIALID=zxwl.FMATERIALID

where fxwl_P.FISMAINPRD = 1 and  fxwl.fmaterialid=@fatherID

union all

--2、递归子查询，

select  p.BOM层次+1 as BOM层次,P.最顶级BOM内码 a