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

ITPub博客

【高海东】SQL语句对表中父子节点正向和反向取所有节点

最新文章