Oracle 中的connect by 语法用于分层查询,它根据构成父子关系的数据按层次顺序返回结果集的行。Oracle 中分层查询的语法如下:
SELECT select_list FROM table_expression [ WHERE ...] [ START WITH start_expression ] CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }
CONNECT BY 子句用于描述父级和子级关系的纽带。CONNECT BY 子句必须由使用等号 (=) 表达式组成。此外,等号 (=) 两边的表达式之一必须前面带有关键字PRIOR ,用于表示父级表达式。
lightdb将在22.4版本中支持Oracle分层查询的语法功能。下面将介绍该特性的功能。
创建表并插入数据。
CREATE TABLE employees ( employee_id serial PRIMARY KEY, full_name VARCHAR NOT NULL, manager_id INT ); INSERT INTO employees ( employee_id, full_name, manager_id ) VALUES (1, 'Michael North', NULL), (2, 'Megan Berry', 1), (3, 'Sarah Berry', 1), (6, 'Bella Tucker', 2), (7, 'Ryan Metcalfe', 2), (10, 'Carolyn Henderson', 3), (11, 'Nicola Kelly', 3);
查询每个员工的所有层级领导
使用SYS_CONNECT_BY_PATH ,可以显示分成查询的层级结构。其语法格式如下:
SYS_CONNECT_BY_PATH (column, delimiter)
它返回column 列从根节点到当前节点的所有列值,列值由delimiter 分隔。
lightdb@postgres=# SELECT lightdb@postgres-# employee_id, lightdb@postgres-# full_name, lightdb@postgres-# SYS_CONNECT_BY_PATH(full_name, '-->') AS "Path" lightdb@postgres-# FROM lightdb@postgres-# employees lightdb@postgres-# CONNECT BY PRIOR employee_id = manager_id lightdb@postgres-# START WITH employee_id = 1; employee_id | full_name | Path -------------+-------------------+---------------------------------------------------- 1 | Michael North | -->Michael North 2 | Megan Berry | -->Michael North-->Megan Berry 3 | Sarah Berry | -->Michael North-->Sarah Berry 6 | Bella Tucker | -->Michael North-->Megan Berry-->Bella Tucker 7 | Ryan Metcalfe | -->Michael North-->Megan Berry-->Ryan Metcalfe 10 | Carolyn Henderson | -->Michael North-->Sarah Berry-->Carolyn Henderson 11 | Nicola Kelly | -->Michael North-->Sarah Berry-->Nicola Kelly (7 rows)
查询每个员工在公司的层级。
指定 LEVEL 关键字显示每行所在分层查询中的层级数。对于结果集中的每一行, LEVEL 返回一个非零整数值,指出由此行表示的节点在层次结构中的深度。根节点的 LEVEL 为 1 。根节点的直接子级的 LEVEL 为 2 ,依此类推。
lightdb@postgres=# SELECT lightdb@postgres-# employee_id, lightdb@postgres-# full_name, lightdb@postgres-# level lightdb@postgres-# FROM lightdb@postgres-# employees lightdb@postgres-# CONNECT BY PRIOR employee_id = manager_id lightdb@postgres-# START WITH employee_id = 1; employee_id | full_name | LEVEL -------------+-------------------+------- 1 | Michael North | 1 2 | Megan Berry | 2 3 | Sarah Berry | 2 6 | Bella Tucker | 3 7 | Ryan Metcalfe | 3 10 | Carolyn Henderson | 3 11 | Nicola Kelly | 3 (7 rows)