示例问题如下:
表结构:
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
表结构:
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
标签:
三种实现方法,数据表中遍历
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
白云城资源网 Copyright www.dyhadc.com
暂无“三种实现方法实现数据表中遍历寻找子节点”评论...
更新日志
2024年10月07日
2024年10月07日
- 群星《前途海量 电影原声专辑》[FLAC/分轨][227.78MB]
- 张信哲.1992-知道新曲与精丫巨石】【WAV+CUE】
- 王翠玲.1995-ANGEL【新艺宝】【WAV+CUE】
- 景冈山.1996-我的眼里只有你【大地唱片】【WAV+CUE】
- 群星《八戒 电影原声带》[320K/MP3][188.97MB]
- 群星《我的阿勒泰 影视原声带》[320K/MP3][139.47MB]
- 纪钧瀚《胎教古典音乐 钢琴与大提琴的沉浸时光》[320K/MP3][148.91MB]
- 刘雅丽.2001-丽花皇后·EMI精选王【EMI百代】【FLAC分轨】
- 齐秦.1994-黄金十年1981-1990CHINA.TOUR.LIVE精丫上华】【WAV+CUE】
- 群星.2008-本色·百代音乐人创作专辑【EMI百代】【WAV+CUE】
- 群星.2001-同步过冬AVCD【环球】【WAV+CUE】
- 群星.2020-同步过冬2020冀待晴空【环球】【WAV+CUE】
- 沈雁.1986-四季(2012梦田复刻版)【白云唱片】【WAV+CUE】
- 纪钧瀚《胎教古典音乐 钢琴与大提琴的沉浸时光》[FLAC/分轨][257.88MB]
- 《国语老歌 怀旧篇 3CD》[WAV/分轨][1.6GB]