Use connect by prior in Oracle to implement recursive query!

Connect by is used in structured queries. Its basic syntax is:
Select... from tablename start by 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.
Prior and start with keywords are optional
The Priory operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, prior
The operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or
Bottom-up. In addition to column names, you can also use column expressions in the join relationship. The start with clause is
Optional, used to identify the node used as the root node for searching the tree structure. If this clause is omitted, it indicates that all
The row of the condition is used as the root node.
Complete example: Select PID, ID, name from t_wf_eng_wfkind start with PID = 0 connect by prior id = PID

The above uses start with for downstream recursive queries on the upper layer... connect by prior... this method, but sometimes when required, it may need reverse recursive queries from the lower layer to the upper layer. This is a statement change: for example, to implement select * from table where ID in ('0', '01', '20180101', '20180101', '20180101 '); now we want to recursive the first-level 03 of 0304, and the second-level 02 of 0203 to recursive, and 01 already exists, with the highest level 0. this table is not only the data, but now I only need to ('0', '01', '123', '123', '123', '02 ', '03 ') in this case, the statement can write select PID, ID, name from v_wf_wfkind_tree where ID in (select distinct (ID) ID from v_wf_wfkind_tree connect by prior pid = ID start with ID in ('0', '01', '123', '123', '123 '));

The value in start with ID in can also replace the select subquery statement.

Note that the difference between the upper-layer and lower-layer recursion lies in the sequence of start with... connect by prior... and slight changes in ID = PID and PID = ID!

From: http://blog.csdn.net/apicescn/archive/2007/02/16/1510922.aspx