CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A產品',0.01
UNION? ALL? SELECT 2,N'B產品',0.02
UNION? ALL? SELECT 3,N'C產品',0.10
UNION? ALL? SELECT 4,N'D配件',0.15
UNION? ALL? SELECT 5,N'E物料',0.03
UNION? ALL? SELECT 6,N'F物料',0.01
UNION? ALL? SELECT 7,N'G配件',0.02

CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION? ALL SELECT 1,7?? --A產品由D配件和G配件組成
UNION? ALL SELECT 2,1
UNION? ALL SELECT 2,6
UNION? ALL SELECT 2,7?? --B產品由F物料及G配件組成
UNION? ALL SELECT 4,5
UNION? ALL SELECT 4,6??? --D配件由F物料組成
UNION? ALL SELECT 3,2
UNION? ALL SELECT 3,1??? --C產品由A產品和B產品組成
GO

CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查詢物料清單及生產量的產品編號列表(逗號分隔)
@Num?? int????????? --要生產的數量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
?DECLARE @Level int
?SET @Level=1
?INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
?FROM Bom a,Item b
?WHERE a.ChildId=b.ID
??AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
?WHILE @@ROWCOUNT>0 and @Level<140
?BEGIN
??SET @Level=@Level+1
??INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
??FROM @t a,Bom b,Item c
??WHERE a.ChildId=b.ItemID
???AND b.ChildId=c.ID
???AND a.Level=@Level-1
?END
?RETURN
END
GO

--調用函數展開產品1、2、3的結構及計算生產10個產品時,各需要多少個配件
SELECT a.ItemID,ItemName=b.Name,
?a.ChildId,ChildName=c.Name,
?a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
?AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId

產品配件清單查詢示例.sql