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

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

示例问题如下：
表结构:
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