SQL语句对表中父子节点正向和反向取所有节点

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，如需转载请自行联系原作者