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