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

        以前公司的同事在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