CREATE TABLE [ dbo ]. [ temptb ]( [ id ] [ int ] IDENTITY( 1, 1) NOT NULL, [ pid ] [ int ] NULL, [ name1 ] [ varchar ]( 20) , [ name ] [ nvarchar ]( 50) , [ parentid ] [ int ] NULL, CONSTRAINT [ PK_temptb ] PRIMARY KEY CLUSTERED ( [ id ] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ PRIMARY ] ) ON [ PRIMARY ] GO /**/ /* 创建函数 根据节点id找出其所有父节点 */ create function f_pid( @id int) returns @re table(id int, level int) as begin declare @l int set @l = 0 insert @re select @id, @l while @@rowcount > 0 begin set @l = @l + 1 insert @re select a.pid, @l from temptb a, @re b where a.id =b.id and b. level = @l - 1 and a.pid <> 0 end update @re set level = @l - level return end go /**/ /* */ select a. *,b. level from temptb a,f_pid( 7) b where a.id =b.id order by b. level go /**/ /* 创建函数 根据节点id 找出所有子节点 */ create function c_tree( @initid int) /**/ /* 定义函数c_tree，输入参数为初始节点id */ returns @t table(id int,name varchar( 100),parentid int,lev INT,byid int) /**/ /* 定义表t用来存放取出的数据 */ begin declare @i int /**/ /* 标志递归级别 */ set @i = 1 insert @t select id,name,parentid, @i ,byid = @initid from temptb where id = @initid while @@rowcount <> 0 begin set @i = @i + 1 insert @t select a.id,a.name,a.parentid, @i, @initid from temptb as a, @t as b where b.id =a.parentid and b.lev = @i - 1 end return END /**/ /* 在上面的函数中由于表变量使用了两次，性能很差 ,下面的性能要高些 */ create function [ dbo ]. [ UF_GetOwnerSKUNumber ]() RETURNS @b table(id int,byid int) BEGIN DECLARE @t table(id int,lev INT,byid int) declare @i int /**/ /* 标志递归级别 */ set @i = 1 insert @t select c.id, @i ,c.byid from [ temptb ] c WITH (NOLOCK) WHERE [ pid ] = 0 OR [ parentid ] IS NULL OR parentid NOT IN ( SELECT id FROM [ temptb ] WHERE id =c.id) while @@rowcount <> 0 begin set @i = @i + 1 insert @b SELECT a.id,b.byid from [ temptb ] as a WITH (NOLOCK) , @t as b where b.id =a.parentid and b.lev = @i - 1 end RETURN END select * from c_tree( ) /**/ /* 把所有行转换为一个字符串 */ DECLARE @FileClassName nvarchar( max) SET @FileClassName = '' SELECT @FileClassName =+ @FileClassName + CONVERT( varchar( 20),id) + ' , ' FROM [ temptb ] a WHERE pid = 0 SELECT @FileClassName AS a

本文转自高海东博客园博客，原文链接：http://www.cnblogs.com/ghd258/archive/2008/05/08/1188592.html，如需转载请自行联系原作者