数据表中遍历寻找子节点的三种实现方法
作者: 小灵, 出处:IT专家网社区, 责任编辑: 李书琴,
2007-12-26 21:30
本文例举了数据表中遍历寻找子节点的三种实现方法……
数据表中遍历寻找子节点的三种实现方法：
示例问题如下：
表结构:
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 |
实现方法三:
代码如下:
| 以下是引用片段： 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 |
- 本文关键词：
