-- 总结关于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