How to use connect by in oracle

In Oracle, "connect by" is used to find data in attribute structures. The syntax is "select * from table [start with condition1] connect by [prior] id=parentid"; where "start with condition1" Used to limit the first layer of data, or root node data, and search for the second layer of data based on this part of the content.

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

How to use connect by in oracle

Basic syntax

select * from table [start with condition1] connect by [prior] id=parentid

Generally used to find data in a tree structure

start with condition1 is used to limit the first layer of data, or root node data; use this part of the data as the basis to find the second layer of data, and then use the second layer of data to find the third layer of data, and so on. .

connect by [prior] id=parentid This part is used to indicate what kind of relationship Oracle uses to search for data; for example, when searching for second-level data, use the id of the first-level data. Match it with the parentid field recorded in the table. If this condition is true, the data found will be the second level data. Similarly, the third level, fourth level, etc. are matched in this way.

level keyword represents the level number in the tree structure; the first level is the number 1, and the second level is the number 2, increasing in sequence.

Examples are as follows:

select rownum from dual connect by rownum<=10;

SELECT TRUNC(SYSDATE - LEVEL) OC_DATE FROM DUAL CONNECT BY LEVEL <= 10

Oracle Video Tutorial》

The above is the detailed content of How to use connect by in oracle. For more information, please follow other related articles on the PHP Chinese website!