文档中心MogDBMogDB StackUqbar
v5.0

文档:v5.0

支持的版本:

其他版本:

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)
Copyright © 2011-2024 www.enmotech.com All rights reserved.