【高海東】SQL語句對錶中父子節點正向和反向取所有節點

iDotNetSpace發表於2008-05-20
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章