BOM

 

CTE_BOM:

 

declare @tb table(FieldID int,ParentID int)

 

insert @tb

select 1,          0 union all

select 3,          0 union all

select 4,          0 union all

select 5,          4 union all

select 19,          5 union all

select 20,          5 union all

select 21,          19 union all

select 31,          20 union all

select 32,          20 union all

select 35,          31 union all

select 36,          32 union all

select 38,          20 union all

select 39,          5

 

--select * from @tb

;with mycte(FieldID,ParentID)

as

(

  select FieldID,ParentID from @tb where FieldID=5

  union all

  select a.FieldID,a.ParentID from @tb a inner join mycte b on a.ParentID=b.FieldID

)

 

select * from mycte

 

Function_BOM:

 

CREATE TABLE BOM(PID INT,ID INT)

INSERT INTO BOM SELECT 801,101

INSERT INTO BOM SELECT 801,102

INSERT INTO BOM SELECT 801,103

INSERT INTO BOM SELECT 801,601

INSERT INTO BOM SELECT 601,101

INSERT INTO BOM SELECT 601,105

INSERT INTO BOM SELECT 601,501

INSERT INTO BOM SELECT 501,106

INSERT INTO BOM SELECT 501,121

 

GO

 

CREATE FUNCTION F_GETROOT(@PID INT)

RETURNS INT

AS

BEGIN

    DECLARE @ID INT

    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)

    BEGIN

        SET @ID=@PID

        SELECT @PID=PID FROM BOM WHERE ID=@ID

    END

    RETURN @PID

END

 

GO

 

SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM

 

GO

 

/*

PID         ID

----------- -----------

801         101

801         102

801         103

801         601

801         101

801         105

801         501

801         106

801         121

*/

 

DROP FUNCTION F_GETROOT

DROP TABLE BOM

GO

 

 

--生成测试数据

create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))

insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'

insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'

insert into BOM_1 select 3 ,'A2','A3',3,'生产'

insert into BOM_1 select 4 ,'A2','A4',2,'采购'

insert into BOM_1 select 5 ,'A3','A5',2,'采购'

insert into BOM_1 select 6 ,'A3','A6',1,'采购'

insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'

insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'

insert into BOM_1 select 9 ,'B2','B3',3,'生产'

insert into BOM_1 select 10,'B2','B4',2,'采购'

insert into BOM_1 select 11,'B3','B5',2,'采购'

insert into BOM_1 select 12,'B3','B6',2,'采购'

go

--创建用户义函数,用于取每个父节点下子节点的采购配置信息

create function f_stock(@bom_head varchar(20))

returns @t table(bom varchar(20),number int)

as

begin

    declare @level int

    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)

    set @level=1

 

    if exists(select 1 from BOM_1 where bom_head=@bom_head)   

    insert into @a

    select bom_child,number,products_attribute,@level

    from BOM_1

    where bom_head=@bom_head

   

    while exists(select 1 from @a where [level]=@level and products_attribute='生产')

    begin

        set @level=@level+1

        insert into @a(bom,number,products_attribute,[level])

        select a.bom_child,a.number,a.products_attribute,@level

        from BOM_1 a,@a b

        where a.bom_head=b.bom and b.[level]=@level-1

    end

   

    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'

    return

end

go

 

--执行调用，取父节点’A'一个标准配置分解的采购信息及数量

select * from dbo.f_stock('A')

 

--删除测试数据

drop function f_stock

drop table BOM_1

 

--生成测试数据

create table BOM(ID INT,PID INT,MSG VARCHAR(1000))

insert into BOM select 1,0,NULL

insert into BOM select 2,1,NULL

insert into BOM select 3,1,NULL

insert into BOM select 4,2,NULL

insert into BOM select 5,3,NULL

insert into BOM select 6,5,NULL

insert into BOM select 7,6,NULL

go

 

--创建用户自定义函数用于取每个父节点下子节点的采购配置信息

create function f_getChild(@ID VARCHAR(10))

returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)

as

begin

    declare @i int

    set @i = 1

    insert into @t select ID,PID,@i from BOM where PID = @ID

   

    while @@rowcount<>0

    begin

        set @i = @i + 1

       

        insert into @t

        select

            a.ID,a.PID,@i

        from

            BOM a,@t b

        where

            a.PID=b.ID and b.Level = @i-1

    end

    return

end

go

 

--执行查询 select ID from dbo.f_getChild(3)

go

 

--输出结果

/*

ID

----

5

6

7

*/

--删除测试数据

drop function f_getChild

drop table BOM

 

 

--创建用户自定义函数,每个子节点的父节点的信息

--生成测试数据

create table BOM(ID int,parentID int,sClassName varchar(10))

insert into BOM values(1,0,'1111'      )

insert into BOM values(2,1,'1111_1'    )

insert into BOM values(3,2,'1111-1-1'  )

insert into BOM values(4,3,'1111-1-1-1')

insert into BOM values(5,1,'1111-2'    )

 

go

 

--创建用户自定义函数,每个子节点的父节点的信息

create function f_getParent(@ID int)

returns varchar(40)

as

begin

    declare @ret varchar(40)

    while exists(select 1 from BOM where ID=@ID and parentID<>0)

    begin

        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')

        from

            BOM a,BOM b

        where

            a.ID=@ID and b.ID=a.parentID

    end

    set @ret=stuff(@ret,1,1,'')

    return @ret

end

go

 

--执行查询 select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM

go

 

--输出结果

/*

ID          parentID                                

----------- ----------------------------------------

1           

2           1

3           1,2

4           1,2,3

5           1  

*/

 

--删除测试数据

drop function f_getParent

drop table BOM

go