海东的技术资料
  博客园 :: 首页 ::  :: 联系 :: 订阅 订阅 :: 管理 ::
  204 随笔 :: 22 文章 :: 664 评论 :: 55 Trackbacks
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
 

posted on 2008-05-08 16:26 高海东 阅读(223) 评论(1)  编辑 收藏 所属分类: SQL SERVER 2000 、SQL SERVER 2005

评论

#1楼  2008-05-08 19:38 晴天水族s [未注册用户]
如果下次加点注释，再说明一下，其未来可用的方向，就更完美了。
没有看明白了，是不是想做了一些类似于用递归完成的无限分级之类的东西？

  回复  引用    


标题  
姓名  
主页
Email (只有博主才能看到) 
验证码 *  看不清,换一张 [登录][注册]
内容(请不要发表任何与政治相关的内容)  
  登录  使用高级评论  新用户注册  返回页首  恢复上次提交      
 
另存  打印
最新IT新闻:
· Mozilla将于本周五发布Firefox 3.1第一个预览版
· 瑞星将向个人用户免费1年
· 中国互联网历史上最伟大的产品TOP10（二）
· 盖茨官方否认天价租楼看奥运 纯属地产商炒作
· 2008年7月24日IT博客精选
博客园新闻频道  博客园首页  社区