【赛迪网－IT技术报道】数据表中遍历寻找子节点的三种实现方法：
示例问题如下：
表结构:
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
1
2
下一页>>
