Use hierarchical queries in Oracle 9i to implement recursive data query in the Tree Structure

Let's take a look at two figures:

This figure shows the order in which Oracle returns a hierarchy tree. A subnode of the root node is returned from the root node,
Then return the child node of the child node until the leaf node.
When a node with a subnode is found and is not an Idan node, the hierarchy increases gradually and Oracle returns the node
After all the "descendants" nodes of the first node of the root node are returned, Oracle searches for the next subnode. This process will continue. (depth first)
(This process continues, with Oracle returning to the root row for the next child
Only when all of the "descendants" of the first child row have been returned .)

This figure illustrates the syntax of hierarchical queries.
1. Do not start with the specified level Root
2. connected by the parent-child relationship in the specified level. In a hierarchical query, the conditional expression must use the prior operator to query the parent-level data.
Analogy:
... Prior expr = expr
Or
... Expr = prior expr
If connect by is qualified, there is only one condition that limits the prior operator. (The example will be given below)
Create a table first and insert some data:

Create   Table Treedemo (
Nodeid nvarchar2 ( 50 ) Default   ''   Not   Null ,
Parent_nodeid nvarchar2 ( 50 ) Default   ''   Not   Null ,
Nodename nvarchar2 ( 100 ) Default   ''   Not   Null
, Constraint Pk_treedemo Primary   Key (Nodeid)
);
Insert   Into Treedemo Values ( ' A ' , 0 , ' A_1 ' );
Insert   Into Treedemo Values ( ' A01 ' , ' A ' , ' A_01 ' );
Insert   Into Treedemo Values ( ' A02 ' , ' A ' , ' A_02 ' );
Insert   Into Treedemo Values ( ' A03 ' , ' A01 ' , ' A_03 ' );
Insert   Into Treedemo Values ( ' A04 ' , ' A01 ' , ' A_02 ' );

Use the lpad and sys_connect_by_path functions to construct the tree structure.
The lpad function fills the string on the left with specific characters and spaces.
Sys_connect_by_path dedicated Oracle tree function

Select Lpad ( '   ' , 2 * Level - 1 ) | Sys_connect_by_path (nodename, ' / ' ) "Tree"
From Treedemo
Start With Nodeid =   ' A04 '  
Connect By Prior nodeid = Parent_nodeid;

Result:

Tree
-- -------------------
  / A_02
/ A_02 / A_01
/ A_02 / A_01 / A_1

Note the following before using start with... connect by PRIO:
If the relationship after PRIO is reversed, the results are different.
Let's take a look at the results of the following two query statements:

1. This query uses the currently qualified node as the root node to traverse and query its subnodes:

Select   *   From Treedemo
Start With Nodeid =   ' A01 '  
Connect By Prior nodeid = Parent_nodeid; Nodeid parent_nodeid nodename
-- -----------------------------
A01 A a_01
A03 A01 a_03
A04 A01 a_03

2. This query will be retrieved from the leaf node traversal to the root node: Start With Nodeid =   ' A04 '  
Connect By Prior parent_nodeid = Nodeid; Nodeid parent_nodeid nodename
-- -----------------------------
A04 A01 a_02
A01 A a_01
A 0 A_1

3. Conditional Filtering:

Select   *   From Treedemo
Start With Nodeid =   ' A04 '  
Connect By Parent_nodeid <> ' 0 '   And Prior parent_nodeid = Nodeid; Nodeid parent_nodeid nodename
-- -----------------------------
A04 A01 a_02
A01 A a_01

You can also add sorting conditions in the query. It can be seen that it is very convenient to process such tree data tables in Oracle, and function processing may be required in ms SQL.