手机推荐排行榜,yy玄幻小说排行榜完本

示例問題如下：
表結(jié)構(gòu):
Id ParentId
1 0
2 1
3 2
......

針對(duì)該表結(jié)構(gòu)解釋如下:
1的父節(jié)點(diǎn)為0,
2的父節(jié)點(diǎn)為1,
3的父節(jié)點(diǎn)為2
......

以此類推,要求給定一個(gè)父節(jié)點(diǎn)的值,比如1,

用SQL語句查詢的到該父結(jié)點(diǎn)下的所有子節(jié)點(diǎn)

下面的Sql是在Sql Server下調(diào)試通過的,如果是Oracle,則有Connect By可以實(shí)現(xiàn).

建立測(cè)試表:

Drop Table DbTree

Create Table DbTree

(

[Id] Int,

[Name] NVarChar(20),

[ParentId] Int

)

插入測(cè)試數(shù)據(jù):

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)

實(shí)現(xiàn)方法一:

代碼如下:

Declare @Id Int

Set @Id = 1 ---在次修改父節(jié)點(diǎn)

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

實(shí)現(xiàn)方法二:

代碼如下:

Create Table #AllRow

(

Id Int,

ParentId Int

)

Declare @Id Int

Set @Id = 1 ---在次修改父節(jié)點(diǎn)

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

實(shí)現(xiàn)方法三:

代碼如下:

在Sql Server2005中其實(shí)提供了CTE[公共表表達(dá)式]來實(shí)現(xiàn)遞歸:

關(guān)于CTE的使用請(qǐng)查MSDN

Declare @Id Int

Set @Id = 3; ---在次修改父節(jié)點(diǎn)

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

標(biāo)簽：珠海福建呼和浩特美容院泉州商洛西寧天水

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《三種實(shí)現(xiàn)方法實(shí)現(xiàn)數(shù)據(jù)表中遍歷尋找子節(jié)點(diǎn)》，本文關(guān)鍵詞三種,實(shí)現(xiàn),方法,數(shù)據(jù),表中,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。

濮阳杆衣贸易有限公司

三種實(shí)現(xiàn)方法實(shí)現(xiàn)數(shù)據(jù)表中遍歷尋找子節(jié)點(diǎn)