147.产品配件清单查询案例

CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N&＃39;A产品&＃39;,0.01
UNION ALL SELECT 2,N&＃39;B产品&＃39;,0.02
UNION ALL SELECT 3,N&＃39;C产品&＃39;,0.10
UNION ALL SELECT 4,N&＃39;D配件&＃39;,0.15
UNION ALL SELECT 5,N&＃39;E物料&＃39;,0.03
UNION ALL SELECT 6,N&＃39;F物料&＃39;,0.01
UNION ALL SELECT 7,N&＃39;G配件&＃39;,0.02CREATE 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产品组成
GOCREATE FUNCTION f_Bom(
&＃64;ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
&＃64;Num int --要生产的数量
)RETURNS &＃64;t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGINDECLARE &＃64;Level intSET &＃64;Level&＃61;1INSERT &＃64;t SELECT a.ItemID,a.ChildId,ROUND(&＃64;Num/(1-b.Wast),0),&＃64;LevelFROM Bom a,Item bWHERE a.ChildId&＃61;b.IDAND CHARINDEX(&＃39;,&＃39;&＃43;RTRIM(a.ItemID)&＃43;&＃39;,&＃39;,&＃39;,&＃39;&＃43;&＃64;ItemIDs&＃43;&＃39;,&＃39;)>0WHILE &＃64;&＃64;ROWCOUNT>0 and &＃64;Level<140BEGINSET &＃64;Level&＃61;&＃64;Level&＃43;1INSERT &＃64;t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),&＃64;LevelFROM &＃64;t a,Bom b,Item cWHERE a.ChildId&＃61;b.ItemIDAND b.ChildId&＃61;c.IDAND a.Level&＃61;&＃64;Level-1ENDRETURN
END
GO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName&＃61;b.Name,a.ChildId,ChildName&＃61;c.Name,a.Nums,a.Level
FROM f_Bom(&＃39;1,2,3&＃39;,10) a,Item b,Item c
WHERE a.ItemID&＃61;b.IDAND a.ChildId&＃61;c.ID
ORDER BY a.ItemID,a.Level,a.ChildId