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.csdn.net/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