【高海东】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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12639172/viewspace-281057/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12639172/viewspace-281057/