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用来存放取出的数据*/begindeclare @i int/**//*标志递归级别*/set @i=1insert @t select id,name,parentid,
@i ,byid
=@initid from temptb
where id
=@initidwhile @@rowcount<>0beginset @i=@i+1insert @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-1endreturnEND/**//*在上面的函数中由于表变量使用了两次,性能很差 ,下面的性能要高些*/create function [dbo].
[UF_GetOwnerSKUNumber]()
RETURNS @b table(id
int,byid
int)
BEGINDECLARE @t table(id
int,lev
INT,byid
int)
declare @i int/**//*标志递归级别*/set @i=1insert @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<>0beginset @i=@i+1insert @b SELECT a.id,b.byid
from [temptb] as a
WITH (NOLOCK) ,
@t as b
where b.id
=a.parentid
and b.lev
=@i-1endRETURN ENDselect * 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,如需转载请自行联系原作者