U8 BOM全阶查询 sql

--BOM全阶查询
declare @ID varchar(50) 
--id输入InvCode
set @ID ='XXXXXXXXXXXXXXXXXXXXXX'
declare @version varchar(50) 
--版本号
set @version ='20'
declare @i int 
set @i = 1
declare @count int 
set @count = 1

select * into tmpnewbomtest
from(
select 1 as lv,SortSeq,a.BomId,b.OpComponentId,d.cInvCode,d.cInvName from bom_bom as a
join bom_opcomponent as b on a.BomId=b.BomId
join bas_part as c on b.ComponentId=c.PartId
join inventory as d on c.InvCode=d.cInvCode
where a.BomId = 
(
select top 1 a.BomId from bom_bom as a
join bom_parent as b on a.BomId=b.BomId
join bas_part as c on b.ParentId=c.PartId
join inventory as d on c.InvCode=d.cInvCode
where c.InvCode = @ID
)
and Version = @version
)aaa

while @i < 10