CREATETABLE[dbo].
[temptb](
[id][int]IDENTITY(
1,
1)
NOTNULL,
[pid][int]NULL,
[name1][varchar](
20) ,
[name][nvarchar](
50) ,
[parentid][int]NULL,
CONSTRAINT[PK_temptb]PRIMARYKEYCLUSTERED (
[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找出其所有父节点*/ createfunction f_pid(
@idint)
returns@retable(id
int,
levelint)
as begin declare@lint set@l=0 insert@reselect@id,
@l while@@rowcount>0 begin set@l=@l+1 insert@reselect a.pid,
@l from temptb a,
@re b
where a.id
=b.id
and b.
level=@l-1 and a.pid
<>0 end update@resetlevel=@l-level return end go /**//**/ select a.
*,b.
level from temptb a,f_pid(
7) b
where a.id
=b.id
orderby b.
level go /**//* 创建函数 根据节点id 找出所有子节点*/ createfunction c_tree(
@initidint)
/**//*定义函数c_tree,输入参数为初始节点id*/ returns@ttable(id
int,name
varchar(
100),parentid
int,lev
INT,byid
int)
/**//*定义表t用来存放取出的数据*/ begin declare@iint/**//*标志递归级别*/ set@i=1 insert@tselect id,name,parentid,
@i ,byid
=@initidfrom temptb
where id
=@initid while@@rowcount<>0 begin set@i=@i+1 insert@tselect a.id,a.name,a.parentid,
@i,
@initidfrom temptb
as a,
@tas b
where b.id
=a.parentid
and b.lev
=@i-1 end return END /**//*在上面的函数中由于表变量使用了两次,性能很差 ,下面的性能要高些*/ createfunction[dbo].
[UF_GetOwnerSKUNumber]()
RETURNS@btable(id
int,byid
int)
BEGIN DECLARE@ttable(id
int,lev
INT,byid
int)
declare@iint/**//*标志递归级别*/ set@i=1 insert@tselect c.id,
@i ,c.byid
from[temptb] c
WITH (NOLOCK)
WHERE[pid]=0OR[parentid]ISNULL OR parentid
NOTIN (
SELECT id
FROM[temptb]WHERE id
=c.id)
while@@rowcount<>0 begin set@i=@i+1 insert@bSELECT a.id,b.byid
from [temptb]as a
WITH (NOLOCK) ,
@tas b
where b.id
=a.parentid
and b.lev
=@i-1 end RETURN END select*from c_tree( )
/**//* 把所有行转换为一个字符串 */ DECLARE@FileClassNamenvarchar(
max)
SET@FileClassName='' SELECT@FileClassName=+@FileClassName+CONVERT(
varchar(
20),id)
+','FROM[temptb] a
WHERE pid
=0 SELECT@FileClassNameAS a