SQL 非递归展开两层

--非递归展开两层
--0层
declare @name nvarchar(15)
	select @name='说明书'

select t1.FMATERIALID into #materialidtable   from T_ENG_BOM t1
left join T_ENG_BOMCHILD t3 on t1.fid=t3.fid
left join T_BD_MATERIALBASE base on base.FMATERIALID = t3.FMATERIALID
left join T_BD_MATERIAL_L l on l.FMATERIALID = t3.FMATERIALID
where (base.FERPCLSID=5 or l.fname like '%'+@name+'%') --and (t1.fnumber like '1.%' or t1.fnumber like '8.%') 

select   a.fid,a.fnumber,0 as 层级,d.fnumber 子项编码 ,d.fnumber as 父项编码,'' F_SRMB_ASSISTANT,
'' 工位,row_number() over(order by a.fid) as   idid,0 fentryid ,0 id2 ,0 FAUXPROPID
,d.fmaterialid,0 FNUMERATOR,0 FDENOMINATOR
into #data0
from T_ENG_BOM a
left join T_BD_MATERIAL d on a.fmaterialid=d.fmaterialid
where   d.fmaterialid in (select FMATERIALID from #materialidtable)

--1层
select   a.fid,a.fnumber,1 as 层级,c.fnumber 子项编码 ,d.fnumber as 父项编码,b.F_SRMB_ASSISTANT,
e.fdatavalue 工位,aa.idid,b.fentryid ,row_number() over(order by b.fentryid) as id2 ,b.FAUXPROPID
,c.fmaterialid,b.FNUMERATOR,b.FDENOMINATOR
into #data001
from T_ENG_BOM a
left join T_ENG_BOMCHILD b on a.fid=b.fid 
left join T_BD_MATERIAL c on b.fmaterialid=c.fmaterialid
left join T_BD_MATERIAL d on a.fmaterialid=d.fmaterialid
left join  T_BAS_ASSISTANTDATAENTRY_L e ON b.F_SRMB_ASSISTANT=e.FENTRYID 
inner join #data0 aa on aa.子项编码=d.fnumber
where a.FFORBIDSTATUS = 'A' and a.FDOCUMENTSTATUS='C'
order by b.fentryid 

--2层
select   a.fid,a.fnumber,2 as 层级 ,c.fnumber 子项编码 ,d.fnumber as 父项编码,b.F_SRMB_ASSISTANT,
e.fdatavalue 工位,aa.idid,b.fentryid ,aa.id2 ,b.FAUXPROPID
,c.fmaterialid,b.FNUMERATOR,b.FDENOMINATOR
into #data002
from T_ENG_BOM a
left join T_ENG_BOMCHILD b on a.fid=b.fid 
left join T_BD_MATERIAL c on b.fmaterialid=c.fmaterialid
left join T_BD_MATERIAL d on a.fmaterialid=d.fmaterialid
left join  T_BAS_ASSISTANTDATAENTRY_L e ON b.F_SRMB_ASSISTANT=e.FENTRYID 
inner join #data001 aa on aa.子项编码=d.fnumber
where a.FFORBIDSTATUS = 'A' and a.FDOCUMENTSTATUS='C'
order by b.fentryid 

select bb.*,
ll.FNAME as  物料名称,
ll.FSPECIFICATION  as  规格型号,
mm2.F_SRMB_TEXT31 客户编码,
case 
when 		base.FERPCLSID = 10 then '资产'
when 		base.FERPCLSID = 9 then '配置'
when 		base.FERPCLSID = 2 then '自制'
when 		base.FERPCLSID = 11 then '费用'
when 		base.FERPCLSID = 12 then '模型'
when 		base.FERPCLSID = 5 then '虚拟'
when 		base.FERPCLSID = 7 then '一次性'
when 		base.FERPCLSID = 13 then '产品系列'
when 		base.FERPCLSID = 3 then '委外'
when 		base.FERPCLSID = 4 then '特征'
when 		base.FERPCLSID = 6 then '服务'
when 		base.FERPCLSID = 1 then '外购'
end 物料属性,
case when  isnull(bb.FAUXPROPID,0)<>0 then	v.FF100001 else mm2.F_SRMB_TEXT2 end AS 尺寸,
case when  isnull(bb.FAUXPROPID,0)<>0 then	r1.FNUMBER else mm2.F_SRMB_TEXT4 end AS 颜色,
case when  isnull(bb.FAUXPROPID,0)<>0 then	r2.FNUMBER else mm2.F_SRMB_TEXT40 end AS 图号,
mm2.F_SRMB_TEXT3 材质,
bb.FNUMERATOR 用量分子,
bb.FDENOMINATOR 用量分母,
case when isnull(cast(bb.FDENOMINATOR as float),0)>0 then 
	round(cast(bb.FNUMERATOR as float)/cast(bb.FDENOMINATOR as float),4)  end 标准用量,
	d2.fname 单位,memo.FMEMO 备注
from (
select * from #data0
union
select * from #data001
union
select * from #data002
) bb
LEFT JOIN  T_BD_MATERIAL_L ll on ll.fmaterialid=bb.fmaterialid
LEFT JOIN  T_BD_MATERIALBASE base on base.FMATERIALID = bb.FMATERIALID
LEFT JOIN  T_BD_FLEXSITEMDETAILV V ON bb.FAUXPROPID=v.FID  
LEFT JOIN  T_BAS_ASSISTANTDATAENTRY R1 ON V.FF100002=R1.FENTRYID   
LEFT JOIN  T_BAS_ASSISTANTDATAENTRY R2 ON V.FF100003=R2.FENTRYID   
LEFT JOIN  T_BD_MATERIAL mm2 on  mm2.fmaterialid=bb.fmaterialid
LEFT JOIN  T_BD_UNIT c2 ON  c2.FUNITID=base.FBASEUNITID    
LEFT JOIN T_BD_UNIT_L d2 ON d2.FUNITID = c2.FUNITID 
LEFT JOIN T_ENG_BOMCHILD_L memo ON memo.FENTRYID=bb.FENTRYID

order by idid,id2,层级,fentryid,子项编码,父项编码

drop table #data0
drop table #data001
drop table #data002
drop table #materialidtable