数据表中遍历寻找子节点的三种实现方法

        以前公司的同事在QQ上发了个面试题过来,让我给看看.这个问题的巧妙实现,
几年前在一个很出名的Delphi论坛上见过.
感觉里面蕴含有很多SQL的技巧,所以整理了一下,希望对大家有帮助吧!

问题如下：

有表结构如下:
 Id  ParentId
 1  0
 2  1
 3  2
 ......
针对该表结构解释如下:
1的父节点为0,
2的父节点为1,
3的父节点为2
......
以此类推,要求给定一个父节点的值,比如1,
用SQL语句查询的到该父结点下的所有子节点

如下的Sql是在Sql Server下调试通过的,如果是Oracle,则有Connect By可以实现.

建立测试表:

Drop   Table  DbTree
Create   Table  DbTree
(
         [ Id ]   Int ,
         [ Name ]   NVarChar ( 20 ),
         [ ParentId ]   Int
)

插入测试数据:

Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 1 , 0 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 2 , 1 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 3 , 1 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 4 , 3 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 5 , 4 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 6 , 7 )
Insert   Into  DbTree ( [ Id ] , [ ParentId ] )  Values  ( 8 , 5 )

实现方法一:
代码如下:

Declare   @Id   Int
Set   @Id   =   1          -- -在次修改父节点
Select   *   Into  # Temp     From  DbTree  Where  ParentId  In  ( @Id ) 
Select   *   Into  #AllRow  From  DbTree  Where  ParentId  In  ( @Id )  -- 1,2

While   Exists ( Select   *   From  # Temp )
Begin
     Select   *   Into  #Temp2  From  # Temp
     Truncate   Table  # Temp

     Insert   Into  # Temp      Select   *   From  DbTree  Where  ParentId  In  ( Select  Id  From  #Temp2)
     Insert   Into  #AllRow   Select   *   From  # Temp
     Drop   Table  #Temp2
End
Select   *   From  #AllRow  Order   By  Id

Drop   Table  # Temp
Drop   Table  #AllRow

实现方法二:
代码如下:

Create   Table  #AllRow
(
    Id   Int , 
    ParentId  Int
) 

Declare   @Id   Int
Set   @Id   =   1          -- -在次修改父节点

Delete  #AllRow

-- 顶层自身
Insert   Into  #AllRow (Id,ParentId)  Select   @Id ,   @Id

While   @@RowCount   >   0
Begin
     Insert   Into  #AllRow (Id,ParentId)
     Select  B.Id,A.Id
     From  #AllRow A,DbTree B
     Where  A.Id  =  B.ParentId  And
     Not   Exists  ( Select  Id  From  #AllRow  Where  Id  =  B.Id  And  ParentId  =  A.Id)
End  

Delete   From  #AllRow  Where  Id  =   @Id
Select   *   From  #AllRow  Order   By  Id
Drop   Table  #AllRow

实现方法三:
代码如下:
在Sql Server2005中其实提供了CTE[公共表表达式]来实现递归:
关于CTE的使用请查MSDN

Declare   @Id   Int
Set   @Id   =   3 ;     -- -在次修改父节点

With  RootNodeCTE(Id,ParentId)
As
(
     Select  Id,ParentId  From  DbTree  Where  ParentId  In  ( @Id )
     Union   All
     Select  DbTree.Id,DbTree.ParentId  From  RootNodeCTE
     Inner   Join  DbTree
     On  RootNodeCTE.Id  =  DbTree.ParentId
)

Select   *   From  RootNodeCTE