Use connect by prior to implement recursive queries in Oracle!

Note: This article refers to other people's articles, linked to: http://blog.csdn.net/apicescn/article/details/1510922, I recorded just for the convenience of viewing

Original:

Connect by
Select ... from tablename start by Cond1
Connect by Cond2
where Cond3;
Simply put a tree structure in a table, for example, there are two fields in a list :
Id,parentid who, you can form a tree-like structure.
query with the above syntax can get all the records of this tree.
cond1 is the definitive statement of the root node, which can, of course, relax the qualification, To get multiple root nodes, it is actually more than one tree.
Cond2 is a join condition with a prior represents the previous record, such as CONNECT by PRIOR id=praentid That means the ID of the previous record Praentid
Cond3 is a filter condition that is used to filter all records returned.

prior and the start with keyword are optional
The Priory operator must precede one of the two columns in the connection relationship. For parent-child relationships between nodes, the PRIOR
operator represents a parent node on one side and a child node on the other, determining whether the find tree structure is in the order of top-down or
-bottom-up. In connection relationships, column expressions are also allowed, in addition to the use of column names. The START with clause is an
option that identifies which node is the root node of the lookup tree structure. If the clause is omitted, all rows that satisfy the query
condition are used as the root node.
Complete examples such as select Pid,id,name from t_wf_eng_wfkind start with PID =0 connect by PRIOR  id = pid

The above is mainly for the upper layer of the downstream recursive query and use start with ... connect by prior ... This way, but sometimes when demand is needed, the reverse recursive query from the lower layer to the upper layer may be required, and this is the statement that changes: for example, to implement select * from table where ID in (' 0 ', ' 01 ', ' 0101 ', ' 0203 ', ' 0304 '); Now I want to give 0304 of the upper level 03 to recursion, 0203 of the upper 02 is recursive, and 01 is now existing, the highest level is 0. And this table is not just the data, but I now only need (' 0 ', ' 01 ', ' 0101 ', ' 0203 ', ' 0304 ', ' 02 ', ' 03 ') of this data, at which point the statement can write the Select Pid,id,name from V_wf_wfkind_tree WHERE ID in (the Select DISTINCT (ID) ID from v_wf_wfkind_tree CONNECT by PRIOR PID = ID START with ID in (' 0 ', ' 01 ', ' 0101 ', ' 0203 ', ' 0304 '));

Where start with the value inside ID can also replace the SELECT subquery statement.

Note that the difference between the recursion of the upper-down layer and the lower-level upward layer is the start with ... CONNECT by PRIOR ... The order of precedence as well as the small change of id = PID and PID = ID!

The

uses connect by prior to implement recursive queries in Oracle!