The start With...connect by prior clause in Oracle uses the connect by as a structured query with the basic syntax:
Select ... from tablename start with condition 1
Connect by Condition 2
Where Condition 3;
Cases:
SELECT * FROM table
Start with org_id = ' hbhqfwgwpy '
Connect by prior org_id = parent_id;
In short, a tree structure is stored in a table, for example, there are two fields in a list:
org_id,parent_id so by indicating who the parent of each record is, a tree structure can be formed.
All records of this tree can be obtained by querying the above syntax.
which
Condition 1 is the limit of the root node, of course, you can relax the qualification to obtain multiple root nodes, is actually more than a tree.
Condition 2 is a join condition in which PRIOR represents the previous record, such as connect by PRIOR org_id = parent_id means that the org_id of the previous record is parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filter condition that is used to filter all records returned.
The following is a brief introduction:
When you scan a tree structure table early, you need to access each node of the tree structure, one node can access only once, and the steps to access it are as follows:
The first step: starting from the root node;
The second step: access the node;
The third step: to determine whether the node has no access to the child node, if any, then to its leftmost sub-section of the left, and perform the second step, otherwise perform the fourth step;
Fourth step: If the node is the root node, the access is complete, otherwise, the fifth step;
Fifth step: Return to the parent node of the node and perform the third step.
In summary: The process of scanning the entire tree structure is also the process of traversing the tree in sequence.
Oracle in Connect by prior recursive query