START with the CONNECT by prior clause for recursive queries

• 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.

• This article comes to: http://jingyan.baidu.com

START with the CONNECT by prior clause for recursive queries