Oracle SQL tree structure query

Time:2020-11-15

The select statement in Oracle can use start with… Connect by prior clause to realize recursive query. Connect by is used in structured query, and its basic syntax is as follows:

Copy codeThe code is as follows:
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 table

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

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

Cond1 is the Qualification Statement of the root node. Of course, the qualification condition can be relaxed to obtain multiple root nodes, which is actually multiple trees.

Cond2 is the connection condition, in which prior is used to represent the previous record. For example, connect by prior id = preantid, that is, 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 the filter condition used to filter all records returned.

Simple tree query for Oracle (recursive query)

DEPTID NUMBER Department ID
PAREDEPTID NUMBER Parent department ID (Department ID)
NAME CHAR (40 Byte) Department name

Trace to the root node through the child node

Copy codeThe code is as follows:
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

Traverse the child nodes through the root node

Copy codeThe code is as follows:
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid

The level keyword can be used to query the level

Copy codeThe code is as follows:
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

PS: start with is followed by the seeds of recursion, that is, where recursion starts;

The order of the fields after connect by prior is particular;

If priority is default, only the starting line that meets the conditions can be queried, and no recursive query is performed;

For example:

Copy codeThe code is as follows:
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 table

org_ id,parent_ ID then a tree structure can be formed by indicating who the parent of each record is.

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

Among them:

Condition 1 is the Qualification Statement of the root node. Of course, it can be relaxed to obtain multiple root nodes, which is actually multiple trees.

Condition 2 is the join condition, where prior is used to represent the previous record,

For example, connect by prior org_ id = parent_ ID is the org of the last record_ ID is the parent of this record_ ID, that is, the father of this record is the previous record.

Condition 3 is the filter condition, which is used to filter all the records returned.

The brief introduction is as follows:

When scanning the tree structure table, you need to access each node of the tree structure according to this. A node can only access once. The steps of accessing are as follows:

Step 1: start from the root node;

Step 2: visit the node;

Step 3: judge whether the node has any unreachable child nodes. If so, turn to the left most untouched child node and execute the second step; otherwise, perform the fourth step;

Step 4: if the node is the root node, the access is completed; otherwise, step 5 is executed;

Step 5: return to the parent node of the node and perform the third step.

In short: the process of scanning the whole tree structure is also the process of traversing the tree in the middle order.

1. Description of tree structure

The tree structure data is stored in the table, and the hierarchical relationship between data is described by the relationship between columns in the table, such as empno and Mgr in EMP table. Empno refers to the number of the employee, Mgr refers to the number of the person who leads the employee, that is, the Mgr value of the child node is equal to the empno value of the parent node. In each row of the table, there is an Mgr representing the parent node (except the root node). Through the parent node of each node, the whole tree structure can be determined.

Using the connect by and start with clauses in the select command, you can query the tree structure relationships in the table. The command format is as follows:

SELECT 。。。

2 columns of prict

[START WITH] ;

Among them, the connect by clause indicates that each row of data will be retrieved in hierarchical order, and specifies that the data in the table will be connected into the relationship of tree structure. The priority operator must be placed before one of the two columns of the join relationship. For the parent-child relationship between nodes, the prior operator represents the parent node on one side and the child node on the other side to determine whether the search tree structure is top-down or bottom-up. In addition to column names, list expressions are allowed in join relationships. 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, it means that all the rows that satisfy the query condition are the root nodes.

Start with: you can specify not only one root node, but also multiple root nodes.

2. About prior

The operator prior is placed before and after the equal sign, which determines the retrieval order.

When prior is placed in front of the equal sign in the connect by clause, it is forced to retrieve from the root node to the leaf node, that is, from the parent node to the child node through the tree structure, which we call the top-down method. For example:

CONNECT BY PRIOR EMPNO=MGR

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

CONNECT BY EMPNO=PRIOR MGR

In this way, you should also specify a starting node.

3. Define the search starting node

In the top-down query tree structure, not only the root node can be started, but also any node can be defined as the starting node to start the downward search. The result of this search is a branch of the structure tree starting with the node.

4. Using level

In a table with tree structure, each row of data is a node in the tree structure. Because the nodes are in different hierarchical positions, each row of records can have a layer number. The layer number is determined according to the distance between the node and the root node. No matter which node you start from, the layer number of the starting root node is always 1, the child node of the root node is 2, and so on.

5. Clipping of nodes and branches

When querying the tree structure, some rows in the table or a branch in the tree can be cut off. The where clause is used to limit the single node in the tree structure to remove the single node in the tree structure, but it does not affect its descendant node (when searching from top to bottom) or the predecessor node (when searching from bottom to top).

6. Sorting display

As in other queries, the order by clause can also be used in tree structure queries to change the display order of query results, instead of traversing the tree structure.

===================Supplement===================

Recursive query with start with… Connect by clause is generally used in the application of maintaining tree structure of a table.

Create a sample table:

Copy codeThe code is as follows:
CREATE TABLE TBL_TEST
(
 ID NUMBER,
 NAME VARCHAR2(100 BYTE),
 PID NUMBER DEFAULT 0
);

Insert test data:

Copy codeThe code is as follows:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘1′,’10’,’0′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘2′,’11’,’1′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘3′,’20’,’0′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘4′,’12’,’1′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘5′,’121′,’2’);

Recursion from root to tree end

Copy codeThe code is as follows:
select * from TBL_TEST
start with id=1
connect by prior id = pid

Recursion from the end to the tree root

Copy codeThe code is as follows:
select * from TBL_TEST
start with id=5
connect by prior pid = id