How to recursively query child nodes in Oracle

In Oracle, you can use the select statement to recursively query child nodes. The function of this statement is to perform a simple query on table data. The syntax is "SELECT * FROM table name START WITH condition 1 CONNECT BY PRIOR condition 2 WHERE Condition 3".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle recursively queries child nodes

Grammar structure

SELECT ... FROM    + 表名  START WITH + 条件1 CONNECT BY PRIOR + 条件2  WHERE  + 条件3

Condition 1: It is a qualifying statement of the root node. Of course, the qualifying conditions can be relaxed to obtain multiple root nodes. That is, multiple trees; in the connection relationship, in addition to listing, column expressions are also allowed. The START WITH clause is optional and is used to identify which node is used as the root node of the search tree structure. If this clause is omitted, it means that all rows that satisfy the query conditions are used as root nodes.

Condition 2: It is a connection condition, in which PRIOR is used to represent the previous record. For example, CONNECT BY PRIOR STUDENT_ID = GRADE_ID, which means that the STUDENT_ID of the previous record is the GRADE_ID of this record, that is, the father of this record is the previous record. Record. 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 a tree-structured relationship. The PRIOR operator must be placed in front of one of the two columns of the join relationship. For parent-child relationships between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side, thereby determining whether the order in which the tree structure is searched is top-down or bottom-up.

Condition 3: It is a filtering condition, used to filter the returned records.

Note:

1, CONNECT BY PRIOR is used in structured queries;

2, the role of START WITH... CONNECT BY PRIOR..., Simply put, it is to store a tree structure in a table.

Traverse to the root node through the child nodes.

select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

Traverse the child nodes through the root node (excluding the root node).

select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid

Traverse the child nodes through the root node (including Root node).

select * from persons.dept start with deptid=0 connect by prior deptid=paredeptid

You can query the level through the level keyword.

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

Remarks:

Usage of start with ...connect by, start with What follows is the seed of recursion.

The seed of the recursion is the place where the recursion starts. The "prior" after connect by. If default: only the starting row that meets the conditions can be queried, and no recursive query will be performed;

The fields placed after connect by prior are relevant and indicate the direction of the query.

Exercise: Get the top node through child nodes

select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid

Table structure

DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)

Department id Parent department id (belonging department id) Department name

Recommended tutorial : "Oracle Video Tutorial"

The above is the detailed content of How to recursively query child nodes in Oracle. For more information, please follow other related articles on the PHP Chinese website!