Oracle tree query, start with connect by prior

SELECT statements in Oracle can use the start with ... Connect by prior clause implements recursive query, connect by is used in structured query, its basic syntax is:

SELECT * FROM tablename start with Cond1

Connect by Cond2

where Cond3;

In short, a tree structure is stored in a table, for example, there are two fields in a list:

Id,parentid then, by representing who the parent of each record is, a tree structure can be formed.

A query with the syntax above can get all the records of this tree.

COND1 is the limit of the root node, of course, can be relaxed to limit the conditions to obtain multiple root nodes, the actual is more than one tree.

COND2 is a join condition in which the previous record is represented by a PRIOR, such as connect by PRIOR Id=praentid The ID of the previous record is the praentid of this record, that is, the father of this record is the previous record.

COND3 is a filter condition that filters all returned records.

Simple tree Query for Oracle (recursive query)

DeptID Number Department ID

Paredeptid Number Parent Department ID (Department ID)

Name CHAR (Byte) department names

The root node is traced through the child nodes.

QL Code SELECT * from persons.dept start with deptid=76 connect by prior Paredeptid=deptid<span style= "Font-size:small" &G T;select * from persons.dept to start with deptid=76 connect by prior paredeptid=deptid</span>

SELECT * from persons.dept start and deptid=76 connect by prior Paredeptid=deptid

Traverses the child nodes through the root node.

QL Code SELECT * from Persons.dept to start with Paredeptid=0 connect by prior Deptid=paredeptid

<span style= "Font-size:small" >select * from persons.dept to start with Paredeptid=0 connect by prior Deptid=paredeptid </SPAN>

SELECT * from persons.dept start and paredeptid=0 connect by prior Deptid=paredeptid

The level keyword can be used to query the hierarchy.

QL Code Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid

<span style= "Font-size:small" >select a.*,level from Persons.dept a start and paredeptid=0 connect by prior deptid= Paredeptid</span>

Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid

The Ps:start with the following is the seed of recursion, the place where recursion begins;

The order of the fields behind the connect by prior is fastidious;

If prior default: can only query to meet the starting line of the condition, do not carry on recursive query;

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 then, by representing who the parent of each record is, a tree structure can be formed.

A query with the syntax above can get all the records of this tree.

which

Condition 1 is the limit statement of the root node, of course, we can relax the qualification to obtain multiple root nodes, in fact, many trees.

Condition 2 is a join condition in which the previous record is represented by prior.

For example, CONNECT by PRIOR org_id = parent_id means that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.

Condition 3 is a filter condition that filters all returned records.

The brief introduction is as follows:

When you scan a tree structure table, you need to access each node of the tree structure, one node can only be accessed once, and its access steps are as follows:

The first step: starting from the root node;

Step Two: Access the node;

The third step: to determine whether the node has no access to the child nodes, if any, then to its leftmost node is not accessed, and perform the second step, otherwise perform step fourth;

The fourth step: If the node is the root node, then the access is complete, otherwise perform the fifth step;

Step Fifth: Return to the parent node of the node and perform the third step.

In short: The process of scanning the entire tree structure is also the process of traversing the tree in the middle sequence.

1. Description of the tree structure

The data in the tree structure is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, which is described by the relationship between the columns in the table and the columns, such as EMPNO and MGR in the EMP table. EMPNO represents the number of the employee, MGR indicates the number of the person who led the employee, that is, the MGR value of the child node equals the EMPNO value of the parent node. In each row of the table, there is a MGR (outside the root node) that represents the parent node, and the entire tree structure can be determined through the parent node of each node.

You can query the tree structure relationships in a table by using the CONNECT by and START with clauses in the SELECT command. Its command format is as follows:

SELECT ...

CONNECT by {PRIOR column name 1 = column name 2| column name 1=prior crack name 2}

[START with];

Where: The Connect by clause indicates that each row of data will be retrieved hierarchically, and that the data in the table is connected to the tree structure relationship. The PRIORY operator must be placed in front of one of the two columns of the connection relationship. For parent-child relationships between nodes, the PRIOR operator represents the parent node on one side and the child node on the other, determining whether the order of the lookup tree structure is top-down or bottom-up. In a connection relationship, you can use column expressions in addition to the column names. The START with clause is optional and is used to identify which node is the root node of the lookup tree structure. If the clause is omitted, all rows that satisfy the query criteria are used as the root node.

START with: Not only can you specify a root node, you can also specify multiple root nodes.

2. About PRIOR

The operator PRIOR is placed at the position before and after the equals sign, determining the order in which the query is retrieved.

When PRIOR is placed in front of the equal sign in the CONNECT by clause, it is forced to retrieve the order from the root node to the leaf node, that is, by the parent node in the direction of the child node through the tree structure, which we call the Top-down approach. Such as:

CONNECT by PRIOR Empno=mgr

When the piror operator is placed after the equal sign in the CONNECT by clause, it forces the sequential retrieval from the leaf node to the root node, that is, the child node is directed to the parent node through the tree structure, which we call the bottom-up approach. For example:

CONNECT by Empno=prior MGR

You should also specify a starting node in this way.

3. Define Lookup Start Node

When querying the tree structure from top to bottom, not only can you start from the root node, you can also define any node as the starting node to start looking down. The result of this lookup is a branch of the structure tree that starts with the node.

4. Use level

In a table with a tree structure, each row of data is a node in the tree structure, so each row can have a layer number because the node is in a different hierarchical position. The layer number is determined based on the distance between the node and the root node. Regardless of which node starts, the start root node's layer number is always 1, the child node of the root node is 2, and so on.

5. Cropping of nodes and branches

When querying a tree structure, you can remove some rows from the table, you can also cut off a branch in the tree, using the WHERE clause to qualify a single node in the tree structure to remove a single node from the tree, but it does not affect its descendant nodes (when it is retrieved from the top) or the predecessor node (when retrieved from the bottom).

6. Sort Display

As in other queries, you can also change the order in which query results are displayed in tree-structured queries, rather than in the order in which they are traversed in the tree structure.