如何用一段SQL语句递归找到一个目录下的所有子目录...

 我有一个存储目录树的表,定义目录间的关系字段有两个:  
  如下:(ClassID表示该目录的编号,ClassParentID表示它的父目录的编号)  
   
  ClassID         ClassParentID  
      1 1  
      2 1  
      3 2  
      4 3  
      5 3  
   
  表示的关系应该是  
   
  -1--(根目录)  
    |  
      -2--(第二级目录)  
        |  
          -3--(第三级目录)  
            |  
              -4--(第四级目录)  
                |  
              -5--(第四级目录)  
   
  这样的目录关系.  
   
  那么我通过知道一个目录的ClassID,如何通过一条SQL语句找到所有该目录的子目录?  
   
  如:  
   
  "SELECT   *   FROM   表   WHERE   ClassParentID   =   "+ClassID+"and   ClassParentID   <>"+ClassID    
   
  但只能找到第一层的子目录,如何找到所有子目录,请大家给一条SQL语句实现!!    
   
  (因为事先不知道目录有多少层,当然一定是一条带有while循环的SQL语句)

 

1、

create   table   Test2  
  (  
  ClassID         int,  
  ClassParentID   int,  
  )  
  go  
   
   
  create   proc   Test2_proc  
  @nClassParentID   int --   要查询的父节点ID   号  
  as  
  begin  
  set   nocount   on  
  --  
  declare   @nClassID   int,  
  @nIndex   int  
  --   create   TempTable  
  create   table   #ClassIDTemp  
  (  
  nIndex   int   identity(1,1),  
  ClassID         int,  
  ClassParentID   int,  
  nFlag   int   default(0),  
  )  
  create   index   #ClassIDTemp_Index   on   #ClassIDTemp(nIndex)  
   
  --   insert   First   RecordSet  
  insert   into   #ClassIDTemp(ClassID,ClassParentID)   select   ClassID,ClassParentID   from   Test2   where   ClassParentID   =   @nClassParentID   order   by   ClassParentID  
  --   Circle  
  while(0=0)  
  begin  
  select   top   1   @nIndex   =   nIndex,   @nClassID   =   ClassID   from     #ClassIDTemp   where   nFlag   =   0   order   by   nIndex   asc    
  if   @@rowcount   =   0  
  break  
  else  
  begin  
  insert   into     #ClassIDTemp(ClassID,ClassParentID)   select   ClassID,ClassParentID   from   Test2     where   ClassParentID   =   @nClassID   order   by   ClassParentID  
  end  
  update     #ClassIDTemp   set   nFlag   =   1   where   nIndex   =   @nIndex  
  end  
   
  --   Select   Result  
  select   ClassID,ClassParentID   from     #ClassIDTemp   order   by   nIndex    
  if   exists(select   *   from   sysobjects   where   name   =   '#ClassIDTemp')  
  drop   table   #ClassIDTemp  
  end  
  go

 

2、

declare   @Table   table   (   ClassID   int   primary   key   ,   ClassParentID   int)  
  declare   @Test   table   (   ClassID   int   primary   key   ,   ClassParentID   int)  
   
  insert   into   @Table   values   (   1,   0   )   --   建议这里是   1，0   而不是   1，   1  
  insert   into   @Table   values   (   2,   1   )  
  insert   into   @Table   values   (   3,   2   )  
  insert   into   @Table   values   (   4,   3   )  
  insert   into   @Table   values   (   5,   3   )  
   
  insert   into   @Test   select   *   from   @Table   where   ClassID   =   2  
   
  while   exists   (   select   *   from   @Table   A  
  inner   join   @Test   B    
  on   A.ClassParentID   =   B.ClassID  
  where   A.ClassID   not   in   (   select   ClassID   from   @Test   )   )  
  begin  
  insert   into   @Test  
  select   A.*   from   @Table   A  
  inner   join   @Test   B    
  on   A.ClassParentID   =   B.ClassID    
  where   A.ClassID   not   in   (   select   ClassID   from   @Test   )  
  end  
   
   
  select   *   from   @Test

 

3、

sql1   =   "(SELECT   ClassID   FROM   表   WHERE   ClassParentID   ="   +   变量   +   ")"  
  sql2   =   "(SELECT   ClassID   FROM   表   WHERE   (ClassParentID   IN   "+   sql1   +   ")   OR   (ClassID   IN   "   +   sql1   +   "))"  
   
  sql   =   "SELECT   *   FROM   表   WHERE   ClassParentID   IN   "   +   sql2   +   "   OR   ClassID   IN   "   +   sql2    
       
     
  这个办法可实现1到4级的所有情况（包括只有2级、3级的情况）

 

4、--我的方法是用自定义函数实现,这样具有通用性  
   
  --测试  
   
  --测试数据  
  create   table   表(ClassID   int,ClassParentID   int)  
  insert   表   select   1,0  
  union   all   select   2,1  
  union   all   select   3,2  
  union   all   select   4,3  
  union   all   select   5,3  
  go  
   
  --自定义函数,返回指定目录下的所有子目录列表  
  create   function   f_child(@ClassID   int)  
  returns   @re   table(ClassID   int,Level   int)  
  as  
  begin  
  declare   @l   int  
  set   @l=0  
  insert   @re   select   @ClassID,@l  
  while   @@rowcount>0     --直接用@@rowcount判断  
  begin  
  set   @l=@l+1  
  insert   @re   select   a.ClassID,@l  
  from   表   a   join   @re   b   on   a.ClassParentID=b.ClassID  
  where   b.level=@l-1  
  end  
  return  
  end  
  go  
   
  --调用函数实现查询  
  select   a.*   from     表   a   join   dbo.f_child(1)   b   on   a.ClassID=b.ClassID  
  go  
   
  --加上缩进效果  
  select   ClassID=space(b.level*4)+cast(a.ClassID   as   varchar),a.ClassParentID  
  from     表   a   join   dbo.f_child(1)   b   on   a.ClassID=b.ClassID  
   
  go  
  --删除测试环境  
  drop   table   表  
  drop   function   f_child  
   
  /*--测试结果  
  ClassID           ClassParentID    
  -----------   -------------    
  1                       0  
  2                       1  
  3                       2  
  4                       3  
  5                       3  
   
  （所影响的行数为   5   行）  
   
  ClassID                 ClassParentID    
  --------------   ----------------  
  10  
          2                     1  
                  3             2  
                          4     3  
                          5     3  
   
  （所影响的行数为   5   行）  
  --*/