Oracle Connect by usage article ____oracle

1. Basic Grammar

SELECT * from table [start and Condition1]
    connect by [prior] Id=parentid1

It is commonly used to find data that has a parent-child relationship, that is, a tree-structured data, and its return data can clearly differentiate each layer of data.

The start with Condition1 is used to limit the first layer of data, or to call the root node data, to find the second tier of data based on this part of the data, and then to find the third tier of data with the second tier of data. Connect by [Prior] Id=parentid This section is used to indicate what kind of relationship Oracle is looking for when looking for data, for example, to find the second tier of data with the ID of the first level of data to match the ParentID field recorded in the table, If this condition is set up then the data found is the second layer of data, the same as the third layer of the fourth layer ... And so on are all matched by this.

Prior also has a usage:

SELECT * from table [start and Condition1]
    connect by id= [Prior] Parentid1

Other features

The level keyword, which represents the hierarchy number in the tree structure, the first layer is the number 1, the second level is 2, incremented sequentially. Connect_by_root method, the value of any field in the result set of the first level assembly point can be obtained; connect_by_root (field name). 2.demo Example

2.1 Finding leaf nodes from the root node

Select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with t.id = 0
CONNECT by prior t.id = T.fid;

2.2 Find the upper node from the leaf node

--First, modify prior keyword location
select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with t.id = 4
CONNECT by T.id = Prior t.fid;

--the second, the sequence of the Id=fid logical relationships after the prior keyword is switched to
select t.*, Level, Connect_by_root (ID) from
  tab_test T
 start with T.id = 4
connect by prior T.fid = T.id;

3. Common examples

3.1 The level two agency code that the person belongs to

Select distinct deptno from
    emp_dept
where dept_level = ' 2 '
     connect by dept_no = Prior parent_dept_no
  
   start with Dept_no in  (select  deptno from  emp where name= ' Zhang Shan ';)
  

3.2 String Segmentation

Like splitting 01#02#03#04, this regular string.

Select Regexp_substr (' 01#02#03#04 ', ' [^#]+ ', 1, rownum) as Newport to 
    dual connect by rownum <= Regexp_count (' 01# 02#03#04 ', ' [^#]+ '];