Oracle recursive query (all subsets under query condition ID)

First, CREATE TABLE tbl_test
(
ID number,
NAME VARCHAR2 (+),
PID number DEFAULT 0
)
INSERT into Tbl_test (id,name,pid) VALUES (' 1 ', ' 10 ', ' 0 ');
INSERT into Tbl_test (id,name,pid) VALUES (' 2 ', ' 11 ', ' 1 ');
INSERT into Tbl_test (id,name,pid) VALUES (' 3 ', ' 20 ', ' 0 ');
INSERT into Tbl_test (id,name,pid) VALUES (' 4 ', ' 12 ', ' 1 ');
INSERT into Tbl_test (id,name,pid) VALUES (' 5 ', ' 121 ', ' 2 ');
Second, format
Select * from .... Where [result filter condition statement]
Start with [and start condition filter statement]
Connect by prior [and intermediate record filter condition statement]
Third, find all subordinates
SELECT * from tbl_test start with id=1 connect by prior Id=pid
Note: This SQL can look up all the subordinates of the id=1 data, note that when writing the SQL statement, the condition of the connect by prior clause is id=pid because it is looking for subordinates from the ID.
Iv. Find All Superiors
SELECT * from tbl_test start with id=5 connect by prior Pid=id
Because you are looking for a superior from the ID, the condition of the connect by prior clause is Pid=d

Oracle recursive query (all subsets under query condition ID)