if object_id('t1')is not null
drop table t1
go
 create   table   t1  
  (    
      id   int   identity(1,1),  
      code   int,  
      parentcode   int  
  )  
  go  
  insert   t1   select   1,null  
  union   all   select   2,1  
  union   all   select   3,1  
  union   all   select   4,2   
    union   all   select   5,3
      union   all   select   6,4
        union   all   select   7,5
   go
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[f_cid]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))  
  drop   function   [dbo].[f_cid]  
  GO   
 
  create   function   f_cid(  
  @id   int  
  )returns   @re   table(code   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.code,@l  
  from   [t1]   a,@re   b  
  where   a.parentcode=b.code   and   b.[level]=@l-1  
  end  
  return  
  end  
  go  
   
   select * from t1
  --调用(查询所有的子)  
  select   a.*,层次=b.[level]   from   [t1]   a,f_cid(2)b   where   a.code=b.code  
  go