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