八度空間

1.如果存在臨時表 刪除臨時表

--drop table #bom 
--drop table #bomchild

2.創建2個臨時表 bom主表跟bom明細表 ，字段根據實際情況創建，我這里是按K3來創建

create table #bom--根據實際情況創建字段
(
FId int identity(1,1),--主鍵
FItemID int,--鏈接物料表的內碼 也可以是物料編碼
FNumber nvarchar(200)--物料編碼
)

create table #bomchild --根據實際情況創建字段
(
FID int identity(1,1),--主鍵
FOrgID int,--#bom里的FId
FParentID int,--#bomchild表里上一層的FID
FLevel int,--層級
FSN nvarchar(200),
FItemID int,--物料表里的內碼，也可以是物料編碼
FQty decimal(28,19),--用量
FBOMInterID int,
FEntryID int
)

3.把需要多階層查詢的父物料加入到bom臨時表里,這里我加入了所有物料

--insert into #bom
--(FItemID,FNumber)
--select Fitemid,FNumber from t_ICItem where FErpClsID in (2,3,5)
insert into #bom
(FItemID,FNumber)
select t.FItemID,t.FNumber from t_ICItem as t
inner join t_Item t5 on t5.FItemID=t.FItemID
left join ICBOM as t6 on t6.FItemID=t.FItemID
left join ICBOMGroup t7 on t7.FInterID=t6.FParentID
where FErpClsID in (2,3,5) and t5.FDeleted=0 order by t.FNumber
--------------------------------------------------------------------------------------

4.把所有bom臨時表里物料的第一層bom先添加到bom明細臨時表里 并設置 FLevel為0  第0階層  以便后續的while循環插入使用
insert into #bomchild
(FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)
select
FId,-1 as FParentID,'0',u1.FItemID,1 as Fqty,t1.FInterID as FBOMInterID,-1 as FEntryID,0
from #bom u1
left join ICBOM t1 on u1.FItemID=t1.FItemID and t1.FUseStatus=1072
order by FItemID
------------------------------------------------------------------------------------

5.while 循環 insert 直到 #bomchild沒數據跟@level<20 ，@level 這個可以根據實際情況自行設置  我這里bom最多20層

declare @level int
set @level=1

while exists(
select 1 from
#bomchild
where FLevel=@level-1
and FItemID in
(select icbom.Fitemid from icbom inner join ICBOMChild on icbom.FInterID=ICBOMChild.FInterID)
)
and @level<20
begin
insert into #bomchild
(FOrgID,FParentID,FSN,
FItemID,FQty,FBOMInterID,
FEntryID,FLevel)
select
u1.FOrgID,u1.FID,u1.FSN+'.'+right('000'+CONVERT(nvarchar(50),t2.Fentryid),3),
t2.Fitemid,--u1.FQty*(t2.FQty/t1.FQty)/(1-t2.FScrap/100)
u1.FQty*(t2.FQty/t1.FQty)/(1-t2.FScrap/100)--t2.FAuxQty
,t2.FInterID,
t2.FEntryID,@level
from #bomchild u1
inner join icbom t1 on u1.FItemID=t1.FItemID
inner join ICBOMChild t2 on t2.FInterID=t1.FInterID and t1.FUseStatus=1072
where u1.FLevel=@level-1

set @level=@level+1
end

------------------------下面是查詢bom多階層語句--------------------------

select #bom.FNumber,t1.FName,t1.FModel,t2.FNumber,t2.FName,t2.FModel,#bomchild.FQty from #bom left join t_ICItem as t1 on #bom.FItemID=t1.FItemID
left join #bomchild on #bom.FId=#bomchild.FOrgID
left join t_ICItem as t2 on #bomchild.FItemID=t2.FItemID
order by #bom.FNumber