有些难度的问题(BOM多级展开)

Question

SQL2005可以递归查询，SQL2000得写函数

Answer 1

先2000再2005吧,准备考虑升级SQL

Answer 2

-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL 
  DROP TABLE t_IC_Item
GOCREATE TABLE t_IC_Item
(
  FItemID int,         --物料内码
  FNumber varchar(200),--物料代码
  FName varchar(200),  --物料名称
  FModel varchar(200), --规格型号
  FUnitID int,         --基本单位内码  
  FErpClsID int        --物料属性(1-外购 2-自制 3-委外加工)
)
GOINSERT 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 
GO--SELECT * FROM t_IC_Item-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
  DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
  FID int,
  FName varchar(20)--,
  --FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
  DROP TABLE t_BOM
GO
CREATE TABLE t_BOM 
( 
  FID int,  --自增列，主键
  FBomNo varchar(200),
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GO
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 
GO-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
  DROP TABLE t_BOMChild
GOCREATE TABLE t_BOMChild 
( 
  FID int, --BOM主表的外键
  FEntryID int, --行号
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GOINSERT t_BOMChild  --CP.001 BOM内容
  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,1INSERT t_BOMChild  --BC.001 BOM内容
  SELECT 2,1,6,1,1 UNION
  SELECT 2,2,7,1,1 INSERT t_BOMChild  --BC.002 BOM内容
  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 INSERT t_BOMChild  --BC.003 BOM内容
  SELECT 4,1,10,1,1 UNION
  SELECT 4,2,11,1,1 UNION
  SELECT 4,3,5,1,1 INSERT t_BOMChild  --BC.004 BOM内容
  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
GOcreate procedure p1
@ID int
as
begin
; with BOM as
(select *, FQty=1, [Level]=0, status=0, Ord=cast(FName as varchar(1000)) 
 from t_IC_Item where FItemID=@ID
  union all
     select I.*, FQty=1, [Level]=B.[Level]+1,status=B.FItemID
, Ord=cast(B.Ord+rtrim(C.FEntryID)+I.FName as varchar(1000)) 
 from t_IC_Item I join t_BOMChild C on I.FItemID=C.FItemID
join BOM B on C.FID=B.FItemID
)
select 序号=Row_Number() over(order by Ord)
, 层次=replicate('.',[Level])+rtrim([Level])
, 物料代码=FNumber
, 物料名称=B.FName
, 物料规格=FModel
, 物料属性=case FErpClsID when 1 then '外购' when 2 then '自制' 
when 3 then '委外加工' else '' end
, 用量=FQty
, 单位=U.FName
, BOM展开状态=case when FItemID in (select status from BOM) then '正常' else '' end
from BOM B join t_Unit U on B.FUnitID=U.FID
end
go
exec P1 1drop procedure P1
drop table t_IC_Item, t_Unit, t_BOM, t_BOMChild

Answer 3

结果：
/*
序号                   层次               物料代码             物料名称             物料规格             物料属性     用量          单位                   BOM展开状态
-------------------- ---------------- ---------------- ---------------- ---------------- -------- ----------- -------------------- -------
1                    0                CP.001           产品1              产品规格1            自制       1           个                    正常
2                    .1               BC.001           半成品1             半成品规格1           自制       1           个                    正常
3                    ..2              ZC.001           主材料1             主材料规格1           外购       1           个                    
4                    ..2              ZC.002           主材料2             主材料规格2           外购       1           个                    
5                    .1               BC.002           半成品2             半成品规格2           自制       1           个                    正常
6                    ..2              ZC.003           主材料3             主材料规格3           外购       1           个                    
7                    ..2              ZC.004           主材料4             主材料规格4           外购       1           个                    
8                    ..2              ZC.002           主材料2             主材料规格2           外购       1           个                    
9                    ..2              BC.003           半成品3             半成品规格3           委外加工     1           个                    正常
10                   ...3             ZC.005           主材料5             主材料规格5           外购       1           个                    
11                   ...3             ZC.006           主材料6             主材料规格6           外购       1           个                    
12                   ...3             BC.004           半成品4             半成品规格4           自制       1           个                    正常
13                   ....4            ZC.007           主材料7             主材料规格7           外购       1           个                    
14                   ....4            ZC.008           主材料8             主材料规格8           外购       1           个                    
15                   ....4            ZC.009           主材料9             主材料规格9           外购       1           个                    
16                   ....4            ZC.011           主材料11            主材料规格11          外购       1           个                    
17                   ....4            ZC.002           主材料2             主材料规格2           外购       1           个                    
18                   .1               ZC.010           主材料10            主材料规格10          外购       1           个                    
19                   .1               ZC.012           主材料12            主材料规格12          外购       1           个                    (19 行受影响)
*/

Answer 4

不清楚你的用量是怎么算的，直接写了个FQty=1，

Answer 5

主要在於產生BOM架構時的速度
sql遞歸可以實現,用delphi語句也可以實現，類似於windows的資源管理器

Answer 6

-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL 
  DROP TABLE t_IC_Item
GOCREATE TABLE t_IC_Item
(
  FItemID int,         --物料内码
  FNumber varchar(200),--物料代码
  FName varchar(200),  --物料名称
  FModel varchar(200), --规格型号
  FUnitID int,         --基本单位内码  
  FErpClsID int        --物料属性(1-外购 2-自制 3-委外加工)
)
GOINSERT 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 
GO--SELECT * FROM t_IC_Item-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
  DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
  FID int,
  FName varchar(20)--,
  --FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
  DROP TABLE t_BOM
GO
CREATE TABLE t_BOM 
( 
  FID int,  --自增列，主键
  FBomNo varchar(200),
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GO
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 
GO-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
  DROP TABLE t_BOMChild
GOCREATE TABLE t_BOMChild 
( 
  FID int, --BOM主表的外键
  FEntryID int, --行号
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GOINSERT t_BOMChild  --CP.001 BOM内容
  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,1INSERT t_BOMChild  --BC.001 BOM内容
  SELECT 2,1,6,1,1 UNION
  SELECT 2,2,7,1,1 INSERT t_BOMChild  --BC.002 BOM内容
  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 INSERT t_BOMChild  --BC.003 BOM内容
  SELECT 4,1,10,1,1 UNION
  SELECT 4,2,11,1,1 UNION
  SELECT 4,3,5,1,1 /*INSERT t_BOMChild  --BC.004 BOM内容
  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
*/
GO
create procedure p1
    @ID int
as
begin
    ; with BOM as
    (select *, FQty=1, [Level]=0, status=0, Ord=cast(FName as varchar(1000)) 
     from t_IC_Item where FItemID=@ID
      union all
     select I.*, FQty=1, [Level]=B.[Level]+1,status=B.FItemID
        , Ord=cast(B.Ord+rtrim(C.FEntryID)+I.FName as varchar(1000)) 
     from t_IC_Item I join t_BOMChild C on I.FItemID=C.FItemID
        join BOM B on C.FID=B.FItemID    
    )
    select 序号=Row_Number() over(order by Ord)
        , 层次=replicate('.',[Level])+rtrim([Level])
        , 物料代码=FNumber
        , 物料名称=B.FName
        , 物料规格=FModel
        , 物料属性=case FErpClsID when 1 then '外购' when 2 then '自制' 
                                when 3 then '委外加工' else '' end
        , 用量=FQty
        , 单位=U.FName
        , BOM展开状态=case when FItemID in (select status from BOM) then '正常' else '' end
    from BOM B join t_Unit U on B.FUnitID=U.FID
endGOexec P1 1drop procedure P1
drop table t_IC_Item, t_Unit, t_BOM, t_BOMChild像这样，我将最后一张BOM去掉，显示不出“BOM未建”，这段代码还得改一下

Answer 7

SQL Server 2000的:
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
GOcreate 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)
    
    while @@rowcount>0
    begin
        set @i=@i+1        insert into @t
        select 
            distinct @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 FID from t_BOM) then '正常' 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 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
goexec sp_test
go
/*
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           个                    正常
13          ....4      12          主材料7               主材料规格7              外购                   1           个                    
14          ....4      13          主材料8               主材料规格8              外购                   1           个                    
15          ....4      14          主材料9               主材料规格9              外购                   1           个                    
16          ....4      16          主材料11              主材料规格11             外购                   1           个                    
17          ....4      7           主材料2               主材料规格2              外购                   1           个                    
18          .1         15          主材料10              主材料规格10             外购                   1           个                    
19          .1         17          主材料12              主材料规格12             外购                   1           个  
*/DROP procedure sp_test
DROP TABLE t_IC_Item
DROP TABLE t_Unit
DROP TABLE t_BOM
DROP TABLE t_BOMChild
GO

Answer 8

-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL 
  DROP TABLE t_IC_Item
GOCREATE TABLE t_IC_Item
(
  FItemID int,         --物料内码
  FNumber varchar(200),--物料代码
  FName varchar(200),  --物料名称
  FModel varchar(200), --规格型号
  FUnitID int,         --基本单位内码  
  FErpClsID int        --物料属性(1-外购 2-自制 3-委外加工)
)
GOINSERT 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 
GO--SELECT * FROM t_IC_Item-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
  DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
  FID int,
  FName varchar(20)--,
  --FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
  DROP TABLE t_BOM
GO
CREATE TABLE t_BOM 
( 
  FID int,  --自增列，主键
  FBomNo varchar(200),
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GO
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 
GO-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
  DROP TABLE t_BOMChild
GOCREATE TABLE t_BOMChild 
( 
  FID int, --BOM主表的外键
  FEntryID int, --行号
  FItemID int,
  FQty decimal(28,10),
  FUnitID int
)
GOINSERT t_BOMChild  --CP.001 BOM内容
  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,1INSERT t_BOMChild  --BC.001 BOM内容
  SELECT 2,1,6,1,1 UNION
  SELECT 2,2,7,1,1 INSERT t_BOMChild  --BC.002 BOM内容
  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 INSERT t_BOMChild  --BC.003 BOM内容
  SELECT 4,1,10,1,1 UNION
  SELECT 4,2,11,1,1 UNION
  SELECT 4,3,5,1,1 /*INSERT t_BOMChild  --BC.004 BOM内容
  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
*/
GO我将最后一张BOM去掉，显示不出“BOM未建”，这个过程要怎么处理呢？

Answer 9

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*/
GOcreate 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
goexec 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