Oracle Database traversal tree structure table

1. Start from the root node and find the child node

/*
Create table test (id int, pid int, name varchar (10 ));
Insert into test values (1, 0, 'n1 ');
Insert into test values (2, 1, 'n12 ');
Insert into test values (3, 1, 'n13 ');
Insert into test values (4, 2, 'n21 ');
Insert into test values (5, 2, 'n22 ');
Insert into test values (6, 3, 'n31 ');
Insert into test values (7, 3, 'n32 ');
Insert into test values (8, 4, 'n211 ');
Insert into test values (9, 4, 'n212 ');
*/
/*
Find the child node from the root node (you can specify the node level)
*/

Select id, pid, name, level from test
-- Where level = 3
Start with id = 2
Connect by prior id = pid;

 

2. Find the root node from the leaf node

/*
Create Table Test (ID int, PID int, name varchar (10 ));
Insert into test values (1, 0, 'n1 ');
Insert into test values (2, 1, 'n12 ');
Insert into test values (3, 1, 'n13 ');
Insert into test values (4, 2, 'n21 ');
Insert into test values (5, 2, 'n22 ');
Insert into test values (6, 3, 'n31 ');
Insert into test values (7, 3, 'n32 ');
Insert into test values (8, 4, 'n211 ');
Insert into test values (9, 4, 'n212 ');
*/
/*
Find the root node from the leaf node
The prior keyword is put together with whom, who is the target?
The root node's parent node cannot be itself, which will lead to an endless loop
*/

Select * from test/* Where conditions */
Start with ID = 7
Connect by ID = prior PID;