K3 BOM 多级展开SQL

CREATE TABLE t_IC_Item(FItemID int,FNumber varchar(200),FName varchar(200),FModel varchar(200),FUnitID int,FErpClsID int)

INSERT t_IC_Item
SELECT  1,'CP.001','产品1'   ,'产品规格1'   ,1,2 UNION
SELECT  2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNION
SELECT  3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNION
SELECT  4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNION
SELECT  5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNION
SELECT  6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNION
SELECT  7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNION
SELECT  8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNION
SELECT  9,'ZC.004','主材料4' ,'主材料规格4' ,1,1 UNION
SELECT 10,'ZC.005','主材料5' ,'主材料规格5' ,1,1 UNION
SELECT 11,'ZC.006','主材料6' ,'主材料规格6' ,1,1 UNION
SELECT 12,'ZC.007','主材料7' ,'主材料规格7' ,1,1 UNION
SELECT 13,'ZC.008','主材料8' ,'主材料规格8' ,1,1 UNION
SELECT 14,'ZC.009','主材料9' ,'主材料规格9' ,1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1 

CREATE TABLE t_Unit(FID int,FName varchar(20))
INSERT t_Unit SELECT 1,'个'

CREATE TABLE t_BOM(FID int,FBomNo varchar(200),FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOM 
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1 

CREATE TABLE t_BOMChild(FID int,FEntryID int,FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOMChild
SELECT 1,1, 2,1,1 UNION
SELECT 1,2, 3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1 UNION
SELECT 2,1, 6,1,1 UNION
SELECT 2,2, 7,1,1 UNION
SELECT 3,1, 8,1,1 UNION
SELECT 3,2, 9,1,1 UNION
SELECT 3,3, 7,1,1 UNION
SELECT 3,4, 4,1,1 UNION
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3, 5,1,1 /*UNION
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5, 7,1,1*/
GO

create procedure sp_test
as
begin
    declare @i int
    declare @t table(Level int,Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
                     FModel varchar(20),FErpClsID int,FQty int,FName1 varchar(20),Status varchar(20))
    
    declare @t1 table(id int identity(1,1),Level varchar(10),Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
                     FModel varchar(20),FErpClsID varchar(20),FQty int,FName1 varchar(20),Status varchar(20))

    set @i=0
    
    insert into @t
    select 
        distinct @i,right('000'+rtrim(a.FItemID),4),a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty,c.FName,'正常'
    from 
        t_IC_Item a,t_BOM b,t_Unit c
    where 
        a.FUnitID=b.FUnitID and b.FUnitID=c.FID
        and
        not exists(select 1 from t_BOM d,t_BOMChild e where d.FID=e.FID and e.FItemID=a.FItemID)
        and
        exists(select 1 from t_BOM m,t_BOMChild n where m.FID=n.FID and M.FID=a.FItemID)
    
    while @@rowcount>0
    begin
        set @i=@i+1

        insert into @t
        select 
            @i,e.Code+right('000'+rtrim(d.FEntryID),4),
            a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty*d.FQty*e.FQty,c.FName,
            case when a.FItemID in(select m.FID from t_BOM m,t_BOMChild n where m.FID=n.FID) then '正常' 
                 when a.FItemID in(select FID from t_BOM) then 'BOM未建'
                 else '' 
            end
        from 
            t_IC_Item a,t_BOM b,t_Unit c,t_BOMChild d,@t e
        where 
            a.FUnitID=b.FUnitID 
            and b.FUnitID=c.FID
            and a.FItemID=d.FItemID 
            and b.FID=d.FID
            and d.FID=e.FItemID
            and e.level=@i-1
    end
    
    insert into @t1(Level,Code ,FItemID ,FNumber,FName,FModel,FErpClsID,FQty,FName1,Status)
    select 
        t.* 
    from 
        (select top 100 percent 
             REPLICATE('.',Level)+rtrim(Level) as level,
             Code,FItemID,FNumber,FName,
             FModel,(case FErpClsID when 1 then '外购' when 2 then '自制'when 3 then '委外加工' else '' end) as FErpClsID,
             FQty,FName1,Status 
         from 
             @t 
         order by 
             code) t
    
    select Id,Level ,FItemID,FName,FModel,FErpClsID,FQty,FName1,Status from @t1 order by code
end
go

exec sp_test
/*
Id          Level      FItemID     FName                FModel               FErpClsID            FQty        FName1               Status               
----------- ---------- ----------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- 
1           0          1           产品1                  产品规格1                自制                   1           个                    正常
2           .1         2           半成品1                半成品规格1              自制                   1           个                    正常
3           ..2        6           主材料1                主材料规格1              外购                   1           个                    
4           ..2        7           主材料2                主材料规格2              外购                   1           个                    
5           .1         3           半成品2                半成品规格2              自制                   1           个                    正常
6           ..2        8           主材料3                主材料规格3              外购                   1           个                    
7           ..2        9           主材料4                主材料规格4              外购                   1           个                    
8           ..2        7           主材料2                主材料规格2              外购                   1           个                    
9           ..2        4           半成品3                半成品规格3              委外加工               1           个                    正常
10          ...3       10          主材料5                主材料规格5              外购                   1           个                    
11          ...3       11          主材料6                主材料规格6              外购                   1           个                    
12          ...3       5           半成品4                半成品规格4              自制                   1           个                    BOM未建
13          .1         15          主材料10               主材料规格10             外购                   1           个                    
14          .1         17          主材料12               主材料规格12             外购                   1           个                    
*/
go


DROP procedure sp_test
DROP TABLE t_IC_Item
DROP TABLE t_Unit
DROP TABLE t_BOM
DROP TABLE t_BOMChild
GO