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

示例问题如下：

表结构:

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