SQL 关于tree存储及提取的话题

 

-- 总结关于tree在mssql中的存储、查询相关代码了。
set nocount on
go
create table bom (
     code char(16) not null primary key
    ,parentcode char(16)
    ,name varchar(32)
    ,description varchar(512)
)
go
-- 指定代码的名称
create function dbo.fn_nameofcode (@code char(16))
returns varchar(32)
as begin
    return isnull((select name from bom where code=@code),'')
end
go
-- 指定代码的上级代码
create function dbo.fn_parentcode (@code char(16))
returns char(16)
as begin
    return isnull((select parentcode from bom where code=@code),'')
end
go
-- 指定代码的顶级代码
create function dbo.fn_topcode (@code char(16))
returns char(16)
as begin
    declare @pcode char(16)
    set @pcode=dbo.fn_parentcode(@code)
    while @pcode > ''
    begin
        set @code=@pcode
        set @pcode=dbo.fn_parentcode(@code)
    end
    return @code
end
go
-- 指定代码的路径代码
create function dbo.fn_codepath (@code char(16))
returns varchar(512)
as begin
    declare @pcode char(16), @codepath varchar(512)
    set @codepath=ltrim(rtrim(@code))
    set @pcode=dbo.fn_parentcode(@code)
    while @pcode > ''
    begin
        set @code=@pcode
        set @codepath=ltrim(rtrim(@code))+'/'+@codepath
        set @pcode=dbo.fn_parentcode(@code)
    end
    return @codepath
end
go
-- 指定代码的路径名称
create function dbo.fn_codenamepath (@code char(16))
returns varchar(512)
as begin
    declare @pcode char(16), @namepath varchar(512)
    set @namepath=dbo.fn_nameofcode(ltrim(rtrim(@code)))
    set @pcode=dbo.fn_parentcode(@code)
    while @pcode > ''
    begin
        set @code=@pcode
        set @namepath=dbo.fn_nameofcode(ltrim(rtrim(@code)))+'/'+@namepath
        set @pcode=dbo.fn_parentcode(@code)
    end
    return @namepath
end
go
-- 指定代码的所有下级条目
create function dbo.fn_subtreeofcode (@code char(16))
returns @subtree table (
     code char(16)
    ,parentcode char(16)
    ,codepath varchar(512)
    ,name varchar(32)
    ,description varchar(512)
    )
as begin
    insert into @subtree 
    select code, parentcode, codepath=dbo.fn_codepath(code), name, description
    from bom
    where parentcode=@code 
    while @@rowcount>0
    begin 
        insert into @subtree 
        select code, parentcode, codepath=dbo.fn_codepath(code), name, description
        from bom a
        where exists (select 1 from @subtree b where a.parentcode=b.code)
        and not exists (select 1 from @subtree b where b.code=a.code)
    end  return
end
go
-- 指定代码的所有叶子
create function dbo.fn_leafeofcode (@code char(16))
returns @subtree table (
     code char(16)
    ,parentcode char(16)
    ,codepath varchar(512)
    ,name varchar(32)
    ,description varchar(512)
    )
as begin
    insert into @subtree 
    select code, parentcode, codepath=dbo.fn_codepath(code), name, description
    from bom
    where parentcode=@code 
    while @@rowcount>0
    begin 
        insert into @subtree 
        select code, parentcode, codepath=dbo.fn_codepath(code), name, description
        from bom a
        where exists (select 1 from @subtree b where a.parentcode=b.code)
        and not exists (select 1 from @subtree b where b.code=a.code)
    end
    -- 删除所有存在下级条目的条目
    delete from @subtree a
    where exists(select 1 from @subtree b
        where b.parentcode=a.code)
    return
end
go

/**//**//**//* 一个例子，源自《这样的数据结构怎样设计TABLE最优》
 * http://topic.youkuaiyun.com/u/20080419/13/3b181995-cbf1-4ab6-9101-90caf3953fd0.html
 */
-- 一级物料: 一级编号,一级物料名称,一级描述
insert into bom values ('A','','一种一级物料','物料A的描述')
insert into bom values ('B','','另一种一级物料','物料B的描述')

-- 二级物料:二级编号,二级物料名称,一级物料名称,二级描述
insert into bom values ('A01','A','二级物料','物料A01的描述')
insert into bom values ('A02','A','二级物料','物料A02的描述')
insert into bom values ('B01','B','二级物料','物料B01的描述')
insert into bom values ('B02','B','二级物料','物料B01的描述')

-- 三级物料:三级编号,三级料物名称,一级物料名称,二级物料名称,三级描述
insert into bom values ('B01-1','B01','三级物料','物料B01-1的描述')
insert into bom values ('B01-2','B01','三级物料','物料B01-2的描述')
insert into bom values ('B02-1','B02','三级物料','物料B02-1的描述')

-- 四级物料:四级编号,四级物料名称,一级物料名称,二级物料名称,三级物料名称,四级描述
insert into bom values ('B01-1.023','B01-1','四级物料','物料B01-1.023的描述')
insert into bom values ('B01-1.219','B01-1','四级物料','物料B01-1.219的描述')
insert into bom values ('B01-1.328','B01-1','四级物料','物料B01-1.328的描述')
insert into bom values ('B01-2.012','B01-2','四级物料','物料B01-2.012的描述')
go

select root = dbo.fn_topcode(code), path=dbo.fn_codepath(code), [|]='|', *
from bom

select *, namepath=dbo.fn_codenamepath(code)
from dbo.fn_subtreeofcode('B01')
order by codepath
go

drop function dbo.fn_nameofcode, dbo.fn_parentcode, dbo.fn_topcode, dbo.fn_codepath, dbo.fn_codenamepath
drop function dbo.fn_subtreeofcode
drop table bom