BOM表
CO_CD(公司编号) ITEMPARENT_CD（父物料） BOM_SQ（序号） CHILD_CD（子物料） QTY（用量）
1000 10C-AD-AT-NAC11-P 1 A30C-SAN0752N01-(14) 1
1000 10C-AD-AT-NAC11-P 2 A055-CA2AUS-L 1
1000 10C-AD-AT-NAC11-P 3 A061-AD-AT-NAC11-L 1
1000 10C-AD-AT-NAC11-P 4 A061-AD-AT-NAC11-IBL 1
1000 10C-AD-AT-NAC11-P 5 A068-AD-00-AC-1620 1
1000 A30C-SAN0752N01-(14) 1 A056-AD-00-AC-1620 1
1000 A30C-SAN0752N01-(14) 2 A054-AD-00-AC-1620 1
1000 A30C-SAN0752N01-(14) 3 A053-AD-00-AC-1620 1
1000 A30C-SAN0752N01-(14) 4 A052-AD-00-AC-1620 1
1000 A30C-SAN0752N01-(14) 5 A051-AD-00-AC-1620 1
物料表
CO_CD（公司编号） ITEM_CD（物料编号） ITEM_NM（物料名称）
1000 10C-AD-AT-NAC11-P AAA
1000 A30C-SAN0752N01-(14) BBB
现在想要展开BOM 效果如下：【 10C-AD-AT-NAC11-P 】是产品编号
leve 序号 物料编号 物料名称 用量
1 1 A30C-SAN0752N01-(14) BBB 1
1 2 A055-CA2AUS-L --- 1
1 3 A061-AD-AT-NAC11-L --- 1
1 4 A061-AD-AT-NAC11-IBL --- 1
1 5 A068-AD-00-AC-1620 --- 1
2 1 【30c下面的物料】
2 2
请问怎么用存储过程输入产品编号展开BOM
------解决方案--------------------
- SQL code
if object_id('[BOM]') is not null drop table [BOM]gocreate table [BOM] (CO_CD int,ITEMPARENT_CD nvarchar(40),BOM_SQ int,CHILD_CD nvarchar(40),QTY int)insert into [BOM]select 1000,'10C-AD-AT-NAC11-P',1,'A30C-SAN0752N01-(14)',1 union allselect 1000,'10C-AD-AT-NAC11-P',2,'A055-CA2AUS-L',1 union allselect 1000,'10C-AD-AT-NAC11-P',3,'A061-AD-AT-NAC11-L',1 union allselect 1000,'10C-AD-AT-NAC11-P',4,'A061-AD-AT-NAC11-IBL',1 union allselect 1000,'10C-AD-AT-NAC11-P',5,'A068-AD-00-AC-1620',1 union allselect 1000,'A30C-SAN0752N01-(14)',1,'A056-AD-00-AC-1620',1 union allselect 1000,'A30C-SAN0752N01-(14)',2,'A054-AD-00-AC-1620',1 union allselect 1000,'A30C-SAN0752N01-(14)',3,'A053-AD-00-AC-1620',1 union allselect 1000,'A30C-SAN0752N01-(14)',4,'A052-AD-00-AC-1620',1 union allselect 1000,'A30C-SAN0752N01-(14)',5,'A051-AD-00-AC-1620',1if object_id('[mono]') is not null drop table [mono]gocreate table [mono] (CO_CD int,ITEM_CD nvarchar(40),ITEM_NM nvarchar(6))insert into [mono]select 1000,'10C-AD-AT-NAC11-P','AAA' union allselect 1000,'A30C-SAN0752N01-(14)','BBB'select * from [BOM]select * from [mono]declare @i nvarchar(50)set @i ='10C-AD-AT-NAC11-P';with PCTEas(select 1 as lvl,bom_sq,CHILD_CD ,QTYfrom BOM where ITEMPARENT_CD = @iunion allselect PCTE.lvl +1,N.bom_sq,N.CHILD_CD,PCTE.QTY*N.QTY as qtyfrom PCTE inner join BOM as Non PCTE.child_cd = N.ITEMPARENT_CD)select * from PCTE/*lvl bom_sq CHILD_CD QTY----------- ----------- ---------------------------------------- -----------1 1 A30C-SAN0752N01-(14) 11 2 A055-CA2AUS-L 11 3 A061-AD-AT-NAC11-L 11 4 A061-AD-AT-NAC11-IBL 11 5 A068-AD-00-AC-1620 12 1 A056-AD-00-AC-1620 12 2 A054-AD-00-AC-1620 12 3 A053-AD-00-AC-1620 12 4 A052-AD-00-AC-1620 12 5 A051-AD-00-AC-1620 1(10 行受影响)
------解决方案--------------------