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

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