CONNECT BY子句在执行分层查询时确定行的父子关系。
CONNECT BY子句具有以下常规形式:
CONNECT BY { PRIOR parent_expr = child_expr |
child_expr = PRIOR parent_expr }
在候选父行上对 parent_expr
求值。如果 FROM
所返回行的 parent_expr = child_expr
结果为 TRUE
,则此行被视为父行的子行。
以下可选子句可以与 CONNECT BY
子句一起指定:
START WITH start_expression
由 FROM
子句(start_expression
的求值结果为 TRUE
)返回的行将成为层次结构的根节点。
ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...]
层次结构的同级行按结果集中的 expression
排序。
说明 PolarDB PostgreSQL版(兼容Oracle)在
CONNECT BY
子句中仅支持AND
运算符,暂不支持其它运算符。CONNECT_BY_ISLEAF
CONNECT_BY_ISLEAF能够判断当前行是否为叶子结点,是则返回1或true,否则返回0或false。
CONNECT_BY_ISLEAF语句示例如下:
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
返回示例如下:
Employee IsLeaf LEVEL Path
--------------- ---------- ---------- -----------------------------------
Russell 0 2 /King/Russell
Tucker 1 3 /King/Russell/Tucker
Bernstein 1 3 /King/Russell/Bernstein
Hall 1 3 /King/Russell/Hall
Olsen 1 3 /King/Russell/Olsen
Cambrault 1 3 /King/Russell/Cambrault
Tuvault 1 3 /King/Russell/Tuvault
Partners 0 2 /King/Partners
King 1 3 /King/Partners/King
Sully 1 3 /King/Partners/Sully
McEwen 1 3 /King/Partners/McEwen
Smith 1 3 /King/Partners/Smith
Doran 1 3 /King/Partners/Doran
Sewall 1 3 /King/Partners/Sewall
Errazuriz 0 2 /King/Errazuriz
Vishney 1 3 /King/Errazuriz/Vishney
...
34 rows selected.
CONNECT_BY_ROOT
CONNECT_BY_ROOT是一元运算符,仅在分层查询中有效。 使用此运算符限定一列时,使用根行中的数据返回列值。 该运算符不仅返回直接父行,而且还返回层次结构中的所有祖先行,从而扩展了层次结构查询的CONNECT BY [PRIOR]条件的功能。
CONNECT_BY_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/Giet