Three implementation methods to realize traversal searching sub-nodes in data table _mssql

The sample questions are as follows:
Table structure:
Id ParentID
1 0
2 1
3 2
......

The table structure is interpreted as follows:
1 of the parent node is 0,
2 of the parent node is 1,
3 of the parent node is 2
......


And so on, you need to give the value of a parent node, such as 1,


All child nodes under the parent node that are queried with SQL statements



The following SQL is debugged under SQL Server, and if it is Oracle, a connect by can be implemented.


To establish a test table:



Drop Table Dbtree

Create Table Dbtree

(

[Id] Int,

[Name] NVarChar (20),

[ParentID] Int

)




To insert test data:



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)



Implementation method One:


The code is as follows:



Declare @Id Int

Set @Id = 1---Modify parent node at time

Select * into #Temp to Dbtree Where ParentID in (@Id)

Select * into #AllRow to 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






Implementation method Two:


The code is as follows:



Create Table #AllRow

(

Id Int,

ParentID Int

)


Declare @Id Int

Set @Id = 1---Modify parent node at time


Delete #AllRow


--the top layer itself

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




Implementation method Three:


The code is as follows:



cte[Common table expressions are actually provided in SQL Server2005 to implement recursion:

For the use of CTE please check MSDN

Declare @Id Int

Set @Id = 3; ---to modify the parent node at a time


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