Use start with… In the SELECT statement... Connect by prior clause for recursive query

Use the start with... connect by prior clause in the SELECT statement to implement recursive query

Today, we found that the SELECT statement in Oracle can implement recursive queries using the start with... connect by prior clause. The connect by clause is used in structured queries. Its basic syntax is:
Select... from tablename start with cond1 connect by cond2 where cond3;
Simply put, a tree structure is stored in a table. For example, a table has two fields:
The ID and parentid indicate the parent of each record to form a tree structure.
You can use the preceding syntax to query all records of the tree.
Cond1 is a limit statement for the root node. Of course, you can relax the limit conditions to obtain multiple root nodes. Actually, it is multiple trees.
Cond2 is the connection condition, where prior represents the previous record. For example, connect by prior id = parentid indicates that the ID of the previous record is the parentid of this record, that is, the father of this record is the previous record.
Cond3 is a filter condition used to filter all returned records.
Simple tree query for Oracle (recursive query)
Deptid paredeptid name
Number char (40 bytes)
Department ID parent department ID (Department ID) department name
Follow up with the root node through the sub-node:

Select * from persons. Dept start with deptid = 76 connect by prior paredeptid = deptid 
Traverse sub-nodes through the root node:

Select * from persons. Dept start with paredeptid = 0 connect by prior deptid = paredeptid 
You can use the level keyword to query the level:

Select a. *, level from persons. dept a start with paredeptid = 0 connect by prior deptid = paredeptid 
Review the usage of start with... connect by. What follows start with is the seeds of recursion.
The seeds of recursion are the places where recursion starts. Connect by is followed by the "prior". If the default value is: only the starting row that meets the condition can be queried, and no recursive query is performed;
The fields placed after connect by prior indicate the direction of the query.
Exercise: Obtain the top node through the subnode

Select first_value (deptid) over (order by level DESC rows unbounded preceding) as firstdeptid from persons. Dept start with deptid = 76 connect by prior paredeptid = deptid
Original article: http://sunsja.javaeye.com/blog/192143.