Oracle in Connect by prior recursive query

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.