v5.0

CONNECT BY

Function

Support oracle-compatible connect by syntax，support data query control of level structure and display level, loop, starting level, etc.
Provide oracle-compatible level query function, which can display data content, data level, path, etc. according to the specified connection relationship, starting conditions, etc. in a tree structure.
Specify the root rows of the level query by the start with condition, and perform a recursive query based on those rows to fetch all child rows, as well as child rows of child rows, etc.
The connect by condition specifies the parent-child relationship between the level to determine all child rows of each row that satisfy the condition.

Precautions

If a connection exists, either in the connect statement, or in the from or where clause, the result set after the connection is fetched first, and then the cascading query is performed.
If there is a where filter condition in the statement, execute the cascading query first before filtering the result set, instead of filtering out the unsatisfied rows and all their children.
You can use the level pseudo-column to view the level where the row is located, sys_connect_by_path to view the path from the root row to the row, and connect_by_root to view the root row, and other auxiliary functions.

Syntax

ConnectBy ::= [ START WITH condition ]  CONNECT BY [ NOCYCLE ] condition [ ORDER SIBLINGS BY expr ]

condition represents the parent column information by the PRIOR modifier, and there can have multiple conditions, as follows:

PRIOR expr = expr
expr = PRIOR expr

Parameter Description

START WITH is a condition that the root node data of the level data needs to meet.

CONNECT BY condition can be a compound condition and supports multiple PRIOR. For example:

CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...

NOCYCLE means that the database returns data even if the data exists in a loop.

Pseudo columns such as level, sys_connect_by_path, connect_by_root, etc.

level indicates the number of layers where the row of data is located.
sys_connect_by_path (cname,’delimiter’) represents the path of the cname column from root to the row.

The connect_by_root modifier modifies the column name and displays the root value of the column.

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id;
Employee   Manager     Pathlen    Path
--------------- ------------ ---------- -----------------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz King 3 /King/Kochhar/Higgins/Gietz

ORDER SIBLINGS BY

Keep the returned data of the level query in order between the same levels. For example:

select t.*, level from employee t
start with t.managerid = 'xx'
connect by prior t.empid = t.managerid
order siblings by name desc;

Examples

create table mogdb210_connectby_01
(id int,
 pid int,
 note varchar(20)
);
insert into mogdb210_connectby_01 values(1,null,'A01');
insert into mogdb210_connectby_01 values(101,1,'X01');
insert into mogdb210_connectby_01 values(201,101,'Y01');
insert into mogdb210_connectby_01 values(202,101,'Y02');
insert into mogdb210_connectby_01 values(301,201,'Z01');
insert into mogdb210_connectby_01 values(302,201,'Z02');
insert into mogdb210_connectby_01 values(303,202,'Z03');
insert into mogdb210_connectby_01 values(304,202,'Z04');

Only add the connect by keyword (each row of the base table is used as the root row to get all the lower rows according to the connect by condition)

select id,pid,note,level,connect_by_root id,connect_by_root pid,connect_by_root note,sys_connect_by_path(note,'/') 
from mogdb210_connectby_01
connect by id = pid 
order by id,pid;
 id  | pid | note | level | connect_by_rootid | connect_by_rootpid | connect_by_rootnote | sys_connect_by_path 
-----+-----+------+-------+-------------------+--------------------+---------------------+---------------------
   1 |     | A01  |     1 |                 1 |                    | A01                 | /A01
 101 |   1 | X01  |     1 |               101 |                  1 | X01                 | /X01
 201 | 101 | Y01  |     1 |               201 |                101 | Y01                 | /Y01
 202 | 101 | Y02  |     1 |               202 |                101 | Y02                 | /Y02
 301 | 201 | Z01  |     1 |               301 |                201 | Z01                 | /Z01
 302 | 201 | Z02  |     1 |               302 |                201 | Z02                 | /Z02
 303 | 202 | Z03  |     1 |               303 |                202 | Z03                 | /Z03
 304 | 202 | Z04  |     1 |               304 |                202 | Z04                 | /Z04
(8 rows)

Add nocycle keyword (without the keyword, if there is a loop between levels will report an error, add the nocycle keyword, a line of the subline if there is a loop to skip the subline and continue to output other results)

select id,pid,note,level,connect_by_root id,connect_by_root pid,connect_by_root note,sys_connect_by_path(note,'/') 
from mogdb210_connectby_01
connect by nocycle id = pid 
order by id,pid;
 id  | pid | note | level | connect_by_rootid | connect_by_rootpid | connect_by_rootnote | sys_connect_by_path 
-----+-----+------+-------+-------------------+--------------------+---------------------+---------------------
   1 |     | A01  |     1 |                 1 |                    | A01                 | /A01
 101 |   1 | X01  |     1 |               101 |                  1 | X01                 | /X01
 201 | 101 | Y01  |     1 |               201 |                101 | Y01                 | /Y01
 202 | 101 | Y02  |     1 |               202 |                101 | Y02                 | /Y02
 301 | 201 | Z01  |     1 |               301 |                201 | Z01                 | /Z01
 302 | 201 | Z02  |     1 |               302 |                201 | Z02                 | /Z02
 303 | 202 | Z03  |     1 |               303 |                202 | Z03                 | /Z03
 304 | 202 | Z04  |     1 |               304 |                202 | Z04                 | /Z04
(8 rows)

Add the siblings keyword (to keep the specified order between sub rows of the same line and between the sub rows of each of its sub rows)

select id,pid,note,level,connect_by_root id,connect_by_root pid,connect_by_root note,sys_connect_by_path(note,'/') 
from mogdb210_connectby_01
connect by id = pid 
order siblings by id,pid;
 id  | pid | note | level | connect_by_rootid | connect_by_rootpid | connect_by_rootnote | sys_connect_by_path 
-----+-----+------+-------+-------------------+--------------------+---------------------+---------------------
   1 |     | A01  |     1 |                 1 |                    | A01                 | /A01
 101 |   1 | X01  |     1 |               101 |                  1 | X01                 | /X01
 201 | 101 | Y01  |     1 |               201 |                101 | Y01                 | /Y01
 202 | 101 | Y02  |     1 |               202 |                101 | Y02                 | /Y02
 301 | 201 | Z01  |     1 |               301 |                201 | Z01                 | /Z01
 302 | 201 | Z02  |     1 |               302 |                201 | Z02                 | /Z02
 303 | 202 | Z03  |     1 |               303 |                202 | Z03                 | /Z03
 304 | 202 | Z04  |     1 |               304 |                202 | Z04                 | /Z04
(8 rows)

Add the prior keyword (this keyword specifies the upper-level column information in the connect by connection condition)

select id,pid,note,level,connect_by_root id,connect_by_root pid,connect_by_root note,sys_connect_by_path(note,'/') 
from mogdb210_connectby_01
connect by prior id = pid 
order by connect_by_root id, level, id;
 id  | pid | note | level | connect_by_rootid | connect_by_rootpid | connect_by_rootnote | sys_connect_by_path 
-----+-----+------+-------+-------------------+--------------------+---------------------+---------------------
   1 |     | A01  |     1 |                 1 |                    | A01                 | /A01
 101 |   1 | X01  |     2 |                 1 |                    | A01                 | /A01/X01
 201 | 101 | Y01  |     3 |                 1 |                    | A01                 | /A01/X01/Y01
 202 | 101 | Y02  |     3 |                 1 |                    | A01                 | /A01/X01/Y02
 301 | 201 | Z01  |     4 |                 1 |                    | A01                 | /A01/X01/Y01/Z01
 302 | 201 | Z02  |     4 |                 1 |                    | A01                 | /A01/X01/Y01/Z02
 303 | 202 | Z03  |     4 |                 1 |                    | A01                 | /A01/X01/Y02/Z03
 304 | 202 | Z04  |     4 |                 1 |                    | A01                 | /A01/X01/Y02/Z04
 101 |   1 | X01  |     1 |               101 |                  1 | X01                 | /X01
 201 | 101 | Y01  |     2 |               101 |                  1 | X01                 | /X01/Y01
 202 | 101 | Y02  |     2 |               101 |                  1 | X01                 | /X01/Y02
 301 | 201 | Z01  |     3 |               101 |                  1 | X01                 | /X01/Y01/Z01
 302 | 201 | Z02  |     3 |               101 |                  1 | X01                 | /X01/Y01/Z02
 303 | 202 | Z03  |     3 |               101 |                  1 | X01                 | /X01/Y02/Z03
 304 | 202 | Z04  |     3 |               101 |                  1 | X01                 | /X01/Y02/Z04
 201 | 101 | Y01  |     1 |               201 |                101 | Y01                 | /Y01
 301 | 201 | Z01  |     2 |               201 |                101 | Y01                 | /Y01/Z01
 302 | 201 | Z02  |     2 |               201 |                101 | Y01                 | /Y01/Z02
 202 | 101 | Y02  |     1 |               202 |                101 | Y02                 | /Y02
 303 | 202 | Z03  |     2 |               202 |                101 | Y02                 | /Y02/Z03
 304 | 202 | Z04  |     2 |               202 |                101 | Y02                 | /Y02/Z04
 301 | 201 | Z01  |     1 |               301 |                201 | Z01                 | /Z01
 302 | 201 | Z02  |     1 |               302 |                201 | Z02                 | /Z02
 303 | 202 | Z03  |     1 |               303 |                202 | Z03                 | /Z03
 304 | 202 | Z04  |     1 |               304 |                202 | Z04                 | /Z04
(25 rows)

Add start with keyword (the keyword is used to get the root line, the condition for the base table for filtering, so that the line to meet the conditions as the root line)

select id,pid,note,level,connect_by_root id,connect_by_root pid,connect_by_root note,sys_connect_by_path(note,'/') 
from mogdb210_connectby_01
start with id = 101
connect by id = pid 
order by id,pid;
 id  | pid | note | level | connect_by_rootid | connect_by_rootpid | connect_by_rootnote | sys_connect_by_path 
-----+-----+------+-------+-------------------+--------------------+---------------------+---------------------
 101 |   1 | X01  |     1 |               101 |                  1 | X01                 | /X01
(1 row)

Issue