多层bom展开_K/3 BOM多级展开

CREATE TABLE #GroupData

(        FLevel    INT              NOT NULL,

FItemID   varchar          NOT NULL,

id        char             NOT NULL

)

Create Index #idx_parentid on #GroupData(FITEMID)

create TABLE #GROUPDATA1 (FLevel INT NOT NULL,

FItemID varchar NOT NULL,

id char NOT NULL)

insert into #GroupData(FLevel,FItemID,id)

select @Level+1,b.FItemID,c.id from bom a join bomc b on a.fid=b.fid

join base c on b.FItemID=c.FItemID where a.FItemID='a'

while EXISTS (select top 1 1 from (select flevel,fitemid,id from #GROUPDATA

where flevel=(select max(flevel)from #GROUPDATA) and id=2) a left join bom b on a.FItemID=b.FItemID

where b.fitemid is not null)

BEGIN

SET @Level=(SELECT MAX(FLEVEL)FROM #GroupData)

INSERT INTO #GROUPDATA1(FLevel,FItemID,id)

se