Three implementation methods to traverse data tables and find Sub-nodes

Example:
Table Structure:
Id ParentId
1 0
2 1
3 2
......

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


Similarly, the value of a parent node, such as 1,


All subnodes under this parent node are queried using SQL statements.



The following SQL statements are successfully debugged under SQL Server. If it is Oracle, Connect By can be used.


Create a test table:



Drop Table DbTree

Create Table DbTree

(

[Id] Int,

[Name] NVarChar (20 ),

[ParentId] Int

)




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 1:


The Code is as follows:



Declare @ Id Int

Set @ Id = 1 --- modify the parent node

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






Method 2:


The Code is as follows:



Create Table # AllRow

(

Id Int,

ParentId Int

)


Declare @ Id Int

Set @ Id = 1 --- modify the parent node


Delete # AllRow


-- 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




Method 3:


The Code is as follows:



In SQL Server2005, the CTE [Public table expression] is provided to implement recursion:

For CTE usage, please refer to MSDN

Declare @ Id Int

Set @ Id = 3; --- modify the parent node


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