当前位置: 电脑软硬件应用网 > 设计学院 > 网络编程 > 数据库 > 正文 |
|
|||
三种实现方法实现数据表中遍历寻找子节点 | |||
2008-5-16 11:55:03 文/Isaac 出处:赛迪网 | |||
数据表中遍历寻找子节点的三种实现方法: 示例问题如下:
Id ParentId 1 0 2 1 3 2 ......
针对该表结构解释如下:
下面的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
Begin Select * Into #Temp2 From #Temp Truncate Table #Temp
Insert Into #AllRow Select * From #Temp Drop Table #Temp2 End Select * From #AllRow Order By Id
Drop Table #AllRow
Create Table #AllRow ( Id Int, ParentId Int )
Set @Id = 1 ---在次修改父节点
Insert Into #AllRow (Id,ParentId) Select @Id, @Id
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
Select * From #AllRow Order By Id Drop Table #AllRow
在Sql Server2005中其实提供了CTE[公共表表达式]来实现递归: 关于CTE的使用请查MSDN Declare @Id Int Set @Id = 3; ---在次修改父节点
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 )
|
|||
关于45IT | About 45IT | 联系方式 | 版权声明 | 网站导航 | |
Copyright © 2003-2011 45IT. All Rights Reserved 浙ICP备09049068号 |