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

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

示例问题如下：

表结构:


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