數(shù)據(jù)結(jié)構(gòu)算法:BOM遍歷算法網(wǎng)狀關(guān)系模型

對(duì)于制造多品種產(chǎn)品的企業(yè)，產(chǎn)品與零部件的關(guān)系不再是簡(jiǎn)單的一對(duì)多而是多對(duì)多關(guān)系。由于大量通用件在不同產(chǎn)品間的相互借用，在樹(shù)狀結(jié)構(gòu)中表現(xiàn)為不同的結(jié)點(diǎn)可以有相同的子樹(shù)；即使在同種產(chǎn)品內(nèi)部，由于一些標(biāo)準(zhǔn)件的重用，同樣使零部件關(guān)系呈現(xiàn)出復(fù)雜的交織。
    在SQL SERVER 2000下測(cè)試通過(guò)BOM遍歷算法，更深入具體的可以繼續(xù)深入：
    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 --物料用量
    )
    AS
    create table [work]--創(chuàng)建臨時(shí)表顯示物料層次
    (
    Lvl int,--層次碼
    itemid varchar(50),-- 物料代碼
    usage float --用量
    )
    create table [result]--創(chuàng)建查詢結(jié)果表
    (
    Seq int IDENTITY (1, 1) NOT NULL,--序號(hào)
    Lvl int,--層次碼
    itemid varchar(50),--物料代碼
    usage float --用量
    )
    declare @lvl int, @curr varchar(50),@usage float
    select top 1 @lvl=1,@curr=@Itemid ,@usage=@num from Bom --查詢物料為A,需求數(shù)量為30
    insert into [work](lvl,itemid,usage) values (@lvl,@curr,@usage)
    while(@lvl>0)
    begin
    if exists (select * from [work] where lvl=@lvl)
    begin
    select top 1 @curr=itemid,@usage=usage from [work] where lvl=@lvl
    insert [result](lvl,itemid,usage) values (@lvl,@curr,@usage)
    delete [work] where lvl=@lvl and itemid=@curr
    insert into [work]
    select @lvl+1,Childid,usage
    from Bom where Parentid=@curr and Parentid<>Childid
    if(@@rowcount>0)
    set @lvl=@lvl+1
    end
    else
    set @lvl=@lvl-1
    end
    select * from [result]--展開(kāi)Bom
    select itemid,SUM(usage)*(select usage from [result] where Lvl=’1’ and Seq=’1’) from [result] where
    Lvl<>’1’ group by itemid--求合計(jì)需求物料總數(shù)
    drop table [work]
    drop table [result]
    GO