急求存储过程，按成品展开BOM的存储过程，在线等
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
[解决办法]
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 行受影响)
------解决方案--------------------
CREATE TABLE #bom (CO_CD VARCHAR(20), ITEMPARENT_CD VARCHAR(30), BOM_SQ INT, CHILD_CD VARCHAR(30), QTY INT)INSERT INTO #bomSELECT '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',1CREATE TABLE #物料表 (CO_CD varchar(10), ITEM_CD varchar(30), ITEM_NM varchar(20))INSERT INTO #物料表SELECT '1000', '10C-AD-AT-NAC11-P', 'AAA' UNION ALLSELECT '1000', 'A30C-SAN0752N01-(14)', 'BBB' ;WITH cte AS ( SELECT [LEVEL]=1,BOM_SQ,a.ITEMPARENT_CD, ITEM_NM,a.QTY,CHILD_CD FROM #bom a JOIN #物料表 b ON a.CO_CD=b.CO_CD AND a.ITEMPARENT_CD=b.ITEM_CD WHERE ITEMPARENT_CD='10C-AD-AT-NAC11-P' UNION ALL SELECT [level]=c.[LEVEL]+1,b.BOM_SQ,b.ITEMPARENT_CD,a.ITEM_NM ,b.QTY,b.CHILD_CD FROM #bom b JOIN cte c ON b.ITEMPARENT_CD=c.CHILD_CD JOIN #物料表 a ON a.CO_CD=b.CO_CD AND a.ITEM_CD=b.ITEMPARENT_CD ) SELECT [LEVEL],BOM_SQ,ITEMPARENT_CD, ITEM_NM,QTY FROM cte/*LEVEL BOM_SQ ITEMPARENT_CD ITEM_NM QTY----------- ----------- ------------------------------ -------------------- -----------1 1 10C-AD-AT-NAC11-P AAA 11 2 10C-AD-AT-NAC11-P AAA 11 3 10C-AD-AT-NAC11-P AAA 11 4 10C-AD-AT-NAC11-P AAA 11 5 10C-AD-AT-NAC11-P AAA 12 1 A30C-SAN0752N01-(14) BBB 12 2 A30C-SAN0752N01-(14) BBB 12 3 A30C-SAN0752N01-(14) BBB 12 4 A30C-SAN0752N01-(14) BBB 12 5 A30C-SAN0752N01-(14) BBB 1(10 行受影响) */
[解决办法]
借用3楼代码：
CREATE PROCEDURE [dbo].[SelectBOM]
@ITEMPARENT_CD VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
;WITH cte AS
(
SELECT [LEVEL]=1,BOM_SQ,a.ITEMPARENT_CD, ITEM_NM,a.QTY,CHILD_CD
FROM #bom a JOIN #物料表 b ON a.CO_CD=b.CO_CD AND a.ITEMPARENT_CD=b.ITEM_CD
WHERE ITEMPARENT_CD=@ITEMPARENT_CD
UNION ALL
SELECT [level]=c.[LEVEL]+1,b.BOM_SQ,b.ITEMPARENT_CD,a.ITEM_NM ,b.QTY,b.CHILD_CD
FROM #bom b JOIN cte c ON b.ITEMPARENT_CD=c.CHILD_CD JOIN #物料表 a
ON a.CO_CD=b.CO_CD AND a.ITEM_CD=b.ITEMPARENT_CD
)
SELECT [LEVEL],BOM_SQ,ITEMPARENT_CD, ITEM_NM,QTY
FROM cte
END
GO
使用时只要：
exec dbo.SelectBOM '10C-AD-AT-NAC11-P'