对于制造多品种产品的企业，产品与零部件的关系不再是简单的一对多而是多对多关系。由于大量通用件在不同产品间的相互借用，在树状结构中表现为不同的结点可以有相同的子树；即使在同种产品内部，由于一些标准件的重用，同样使零部件关系呈现出复杂的交织。
在SQL SERVER 2000下测试通过BOM遍历算法，更深入具体的可以继续深入：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Bom]GO
CREATE TABLE [dbo].[Bom] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Parentid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Childid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Usage] [float] NOT NULL , [Price] [money] NOT NULL , [StartDate] [smalldatetime] NOT NULL , [EndDate] [smalldatetime] NOT NULL ) ON [PRIMARY]GO
CREATE PROCEDURE [dbo].[QueryBom] (@Itemid varchar(50),--根物料编码@num float --物料用量)AScreate table [work]--创建临时表显示物料层次(Lvl int,--层次码itemid varchar(50),-- 物料代码usage float --用量)create table [result]--创建查询结果表( Seq int IDENTITY (1, 1) NOT NULL,--序号 Lvl int,--层次码 itemid varchar(50),--物料代码 usage float --用量)declare @lvl int, @curr varchar(50),@usage floatselect top 1 @lvl=1,@curr=@Itemid ,@usage=@num from Bom --查询物料为A,需求数量为30insert into [work](lvl,itemid,usage) values (@lvl,@curr,@usage)while(@lvl>0) beginif exists (select * from [work] where lvl=@lvl)beginselect top 1 @curr=itemid,@usage=usage from [work] where lvl=@lvlinsert [result](lvl,itemid,usage) values (@lvl,@curr,@usage)delete [work] where lvl=@lvl and itemid=@currinsert into [work]select @lvl+1,Childid,usage from Bom where Parentid=@curr and Parentid<>Childidif(@@rowcount>0)set @lvl=@lvl+1end elseset @lvl=@lvl-1endselect * from [result]--展开Bomselect itemid,SUM(usage)*(select usage from [result] where Lvl='1' and Seq='1') from [result] whereLvl<>'1' group by itemid--求合计需求物料总数drop table [work]drop table [result]
GO
