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