PolarDB：層次查詢偽列

層次查詢偽列僅在層次查詢中有效，要在查詢中定義層次關係，必須使用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)