示例問題如下：
表結構:
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
