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

实现方法二:

代码如下:

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[公共表表达式]来实现递归:

记者百度（baidu.com）关于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