oracle--Tree self-correlating table query sql

Usually use the self-related tree structure table to store the data of the tree structure, the hierarchical relationship between the data represents the parent-child relationship in the tree structure, and is usually described by the relation between the two columns in the table, such as the ID and parent_id,id in the following table is the family member number, PARENT_ID is the parent number, Thus, a tree-shaped structure can be formed in a family relationship.

First, basic usage

Basic statement: Select From Connect by prior Start with Order siblings by

The table query statement for the family tree is:

Select * from family_tree Connect by prior id = parent_id Star T with appellation = ' Grandpa ' order siblings by age Desc

The query results are as follows: (from ' Grandpa ' to future generations, and sibling nodes are sorted by age from big to small)

Ii. Connect by & where pruning and de-dot

Connect by indicates that each row of data is retrieved in a hierarchical order and specifies what rules to associate the data with.

Not only that, connect by also restricts the branch of the query, pruning by restrictions: or a previous query, but this time requires not to query ' sister-in-arms ' and their children

Select * from family_tree Connect by prior id = parent_id and   Appellation <> ' sister-in-the-----' start with appellation = ' Grandpa ' order siblings by age desc

The query results are as follows: or the previous query, but this time asked not to inquire ' sister-in-the----and their children

If you just want to not query ' sister-in-the--------but their children still need to be queried, you need to use where to restrict, that is, to only remove single or multiple nodes:

Select * from family_tree where appellation <> ' sister -in- Connect by prior id = parent_id start with appellation = ' Grandpa ' order Sib Lings by age Desc

The results of the query are as follows: ' sister-in-waiting ' node is removed, and its children node is still there.

The WHERE clause cannot be followed by the Connect by clause, or an error will occur.

Iii. about Prior

Prior needs to be used with connect by, and must be placed in front of a column in two columns of a link relationship to mark which column is a ' child id '. This determines the order in which they are retrieved.

If Prior is placed in front of the ' child ID ', the retrieval order is retrieved in the order of the top-down (from root to leaf). (At this point the SQL statement identifies the correct ' child ID ')

If Prior is placed before the ' parent ID ', the retrieval order is retrieved in the order of the bottom-up (from leaf to root). (At this point the SQL statement identifies the wrong ' child ID ' and the ' parent ID ' is mistaken for ' child ID ' so the retrieval direction is exactly the opposite)

For example, the following statement, put prior before the parent_id (parent identity), the search direction is descendants to grandpa retrieval.

Select * from family_tree connect by id = prior parent_id start with appellation = ' cousin ' order siblings by age Desc

The query results are as follows:

Iv. start with definition find starting node

The start with clause specifies from which or which nodes the tree should be retrieved. If the start with clause is omitted, all nodes are the start node.

For example, the following statement only queries the branch with ' Uncle ' and ' sister-in-arms ' as the starting node.

Select * from family_tree connect by prior id = parent_id start with appellation = ' Uncle ' or appellation = ' sister-in-the-brother ' order siblings by /c11> Age desc

The query results are as follows:

Order siblings by the sibling nodes of the same hierarchy

Order siblings by name is the sort between sibling nodes of the same hierarchy, and all of the above statements use this sort.

Select * from family_tree Connect by prior id = parent_id Star T with appellation = ' Grandpa ' order siblings by age ASC

Query Result:

Similarly, the ORDER BY clause can be applied, but all nodes are sorted together, which disrupts the tree structure

Select * from family_tree Connect by prior id = parent_id start with appellation = ' Grandpa ' order by age ASC

VI. Pseudo-Column level

The level needs to be used with the Connect by clause to represent the hierarchy of nodes in the tree structure, the root node is 1, the child nodes of the root node are 2, and so on. The root node here is the starting node of the start with restriction.

The following statement:

Select level, t.* from family_tree T Connect by prior id = parent_id start with appellation = ' Grandpa ' order by age Desc

The query results are as follows, including the Level field

VII. clause execution order: from---start with-connect by------select----order BY

Ps:

1, where is executed after connect by (although the writing is required to write in front), so where only the node does not cut the branch (after the tree relational structure is queried, only through the Where to restrict filtering).

2. Order BY is the last execution, so using order by without order siblings by will disrupt an existing tree-structured search, ordered only by the ORDER BY clause.

8. Pre-sequence traversal

A tree-shaped query clause is a recursive process in which the tree's root node recursively queries the leaf nodes, and the traversal order is carried out according to the tree's pre-order traversal.

oracle--Tree self-correlating table query sql