层次查询伪列

层次查询伪列仅在层次查询中有效，要在查询中定义层次关系，必须使用CONNECT BY子句。层次查询伪列如下：

• CONNECT_BY_ISCYCLE

• CONNECT_BY_ISLEAF

• LEVEL

CONNECT_BY_ISCYCLE

CONNECT_BY_ISCYCLE用于标识层次遍历过程中环的起始位置。

如果当前节点的子节点中也存在其祖先节点， CONNECT_BY_ISCYCLE伪列返回1，否则返回0。

下图中B1节点即为环的起始位置，其子节点C1同时是节点B1的子节点和祖先节点。

只有指定了CONNECT_BY_ISCYCLE子句的NOCYCLE，才能指定CONNECT_BY_ISCYCLE。

使用NOCYCLE可以避免由于数据中存在循环而导致CONNECT BY失败。

示例

-- CREATE TEST TABLE
CREATE TABLE connect_by_table(id INT, pid INT, name TEXT);
INSERT INTO connect_by_table VALUES(1, NULL, 'A');
INSERT INTO connect_by_table VALUES(101, 1, 'B1');
INSERT INTO connect_by_table VALUES(102, 1, 'B2');
INSERT INTO connect_by_table VALUES(10101, 101, 'C0');
INSERT INTO connect_by_table VALUES(10102, 101, 'C1');
INSERT INTO connect_by_table VALUES(10201, 102, 'C2');
INSERT INTO connect_by_table VALUES(102, 10201, 'CYCLE');

SELECT *, CONNECT_BY_ISCYCLE FROM connect_by_table WHERE CONNECT_BY_ISCYCLE=1 START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;
  id   | pid | name | connect_by_iscycle
-------+-----+------+--------------------
 10201 | 102 | C2   |                  1
(1 row)

CONNECT_BY_ISLEAF

如果当前行为层次遍历中的叶子节点，CONNECT_BY_ISLEAF返回1，否则返回0。

下图中的C0, C1均为层次遍历中叶子节点，因此在查询过程中，CONNECT_BY_ISLEAF返回1，其他节点返回0。

示例

SELECT name, CONNECT_BY_ISLEAF FROM connect_by_table START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;
 name | connect_by_isleaf
------+-------------------
 A    |                 0
 B1   |                 0
 C0   |                 1
 C1   |                 1
 B2   |                 0
 C2   |                 0
(6 rows)

LEVEL

对于层次查询返回的每一行，LEVEL伪列为根返回1，为根的子节点返回2，依此类推。如下图的Level 1， Level 2和Level 3。

示例

SELECT id, name, LEVEL FROM connect_by_table START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;
  id   | name | level
-------+------+-------
     1 | A    |     1
   101 | B1   |     2
 10101 | C0   |     3
 10102 | C1   |     3
   102 | B2   |     2
 10201 | C2   |     3
(6 rows)