START with the CONNECT by prior clause for recursive queries

Source: Internet
Author: User

  • START with CONNECT by prior this syntax is primarily used to query the tree-structured relationships in a packet. What happens when you look at the raw data first?

  • The first row of 1001 in the table is the parent of 1002, and the second row 1002 is the parent of 1003, so it loops. Title: Ask for one of the numbers to find its ultimate root node! How is it supposed to be done? Look at the SQL statement below and I'll take 1008 for example. The first num1 in the first row of the graph is the root node. Then parse the SQL statement:

    Select Num1,num2,level

    From Carol_tmp

    Start with num2=1008

    Connect by Num2=prior NUM1 ORDER by level DESC;

    The left and right position of the prior determines whether the retrieval is bottom-up or top-down . Obviously, the above SQL chooses bottom-up, so the root node is eventually obtained.

  • This request obtains the smallest leaf node, see:

    The NUM1 of the first line is the smallest leaf node, and look at the difference between the SQL code and the code above:

    Select Num1,num2,level

    From Carol_tmp

    Start with num2=1008

    Connect by Prior num2= NUM1 ORDER by level DESC;

    This time prior and num2 together, he meant to start looking for the smallest leaf node under it from Num2.

  • Why is it more important for prior to be with someone? For example, prior and num2 are on the same side of the equals sign, and this time depends on the relationship between Num2 and NUM1. If Num2 has been the parent, then this SQL is looking for the root node.

  • Okay, simple recursive query principle is finished, I hope we go back to experiment, even if there is no principle, but at least the first will be used again.

START with the CONNECT by prior clause for recursive queries

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.