CONNECT BY

功能描述

提供兼容oracle的connect by语法，实现层级结构的数据查询控制，并展示等级、循环、起始层级等。
提供oracle兼容的层级查询功能，可以按照指定的连接关系，起始条件等按照树状结构展示数据内容，数据层级，路径等。
通过start with条件指定层级查询的根行，根据这些行进行递归查询来获取所有子行，以及子行的子行等。
通过connect by条件指定层级间父行与子行关系，来确定每一行满足条件的所有子行。

注意事项

如果存在连接，无论是连接语句，还是from或where子句中，先获取连接后的结果集，然后再进行层级查询。
语句中如果存在where过滤条件，先执行层级查询后再将结果集进行过滤，而不是过滤掉不满足的行以及其所有子行。
可以通过level伪列查看该行所在层级，sys_connect_by_path查看从根行到该行的路径，以及connect_by_root查看根行等辅助功能。

语法格式

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

CONNECT BY 的condition通过prior修饰符来表示父级的列信息，如下，可以为多个条件

PRIOR expr = expr
expr = PRIOR expr

参数说明

START WITH是层级数据的根节点数据需要满足的条件。

CONNECT BY condition可以是复合条件，可以有多个PRIOR，例如

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表示即使数据存在循环的条件下也使数据库返回数据

level、sys_connect_by_path、connect_by_root等伪列

level表示该行数据所在的层数
sys_connect_by_path (cname,’delimiter’) 表示cname列从root到该行的路径

connect_by_root修饰符修饰列名，显示该列的root值

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

使层级查询的返回数据在相同层之间保持有序，例如

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

示例

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');

只有connect by关键字（基础表的每一行都做为根行按照connect by条件来获取所有的下层行）

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)

加nocycle关键字（没有该关键词时，如果层级之间存在循环会报错，增加nocycle关键字，某一行的子行如果存在循环就跳过该子行，继续输出其他结果）

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)

加siblings关键字（使同一行的子行之间以及其每一个子行的子行之间保持指定顺序）

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)

加prior关键字（该关键字在connect by连接条件中指定上层列信息）

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)

加start with关键字（该关键字用于获取根行，条件对于基础表进行过滤，使满足条件的行做为根行）

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)

反馈