首批通过分布式安全可靠测评，为关键业务系统打造
层次查询伪列
更新时间：2026-04-10 14:31:27
编辑
分享
层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 CONNECT BY 子句。本文主要介绍层次查询伪列的三种类型的使用与说明，包括 CONNECT_BY_ISCYCLE 伪列、CONNECT_BY_ISLEAF 伪列和 LEVEL 伪列。
CONNECT_BY_ISCYCLE 伪列
CONNECT_BY_ISCYCLE 伪列用来协助标记循环是从哪一行开始的。
如果当前行的子节点同时也是其祖先节点之一，则 CONNECT_BY_ISCYCLE 返回 1，否则返回 0。
CONNECT_BY_ISCYCLE 需要配合 CONNECT BY 子句的 NOCYCLE 使用，否则查询结果会因树状结果存在循环而报错。
CONNECT_BY_ISLEAF 伪列
CONNECT_BY_ISLEAF 伪列用来协助标记层次结构的叶子节点。
如果当前行是由 CONNECT BY 条件定义的树的叶子节点时，则 CONNECT_BY_ISLEAF 返回 1，否则返回 0。
LEVEL 伪列
LEVEL 伪列用来协助标记节点的层次。
在层次结构中，根为第 1 层，根的子结点为第 2 层，之后以此类推。例如，根节点的 LEVEL 值会返回 1，根节点的子节点的 LEVEL 值会返回 2，之后以此类推。
以四层级倒置树结构为例，Root Row 是倒置树中最高的行，LEVEL 值一般为 1。Child Row 是任何非 Root Row，LEVEL 值一般为 2、3 或 4。Parent Row 是任何有 Child Row 的行（Root Row 除外），LEVEL 值一般为 2 或 3。Leaf Row 是任何没有孩子节点的行，LEVEL 值一般为 4。
层次查询示例
CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT);
INSERT INTO tbl1 VALUES(1, 0, -1);
INSERT INTO tbl1 VALUES(2, 1, -2);
INSERT INTO tbl1 VALUES(4, 2, -4);
INSERT INTO tbl1 VALUES(5, 2, -5);
INSERT INTO tbl1 VALUES(3, 1, -3);
INSERT INTO tbl1 VALUES(6, 3, -6);
INSERT INTO tbl1 VALUES(7, 3, -7);
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT col1,CONNECT_BY_ROOT col2 FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOTCOL1 | CONNECT_BY_ROOTCOL2 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 |
| 2 | 1 | 2 | 0 | 0 | 1 | 0 |
| 4 | 2 | 3 | 1 | 0 | 1 | 0 |
| 5 | 2 | 3 | 1 | 0 | 1 | 0 |
| 3 | 1 | 2 | 0 | 0 | 1 | 0 |
| 6 | 3 | 3 | 1 | 0 | 1 | 0 |
| 7 | 3 | 3 | 1 | 0 | 1 | 0 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
7 rows in set
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT (col1 + col2) FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+----------------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOT(COL1+COL2) |
+------+------+-------+-------------------+--------------------+----------------------------+
| 1 | 0 | 1 | 0 | 0 | 1 |
| 2 | 1 | 2 | 0 | 0 | 1 |
| 4 | 2 | 3 | 1 | 0 | 1 |
| 5 | 2 | 3 | 1 | 0 | 1 |
| 3 | 1 | 2 | 0 | 0 | 1 |
| 6 | 3 | 3 | 1 | 0 | 1 |
| 7 | 3 | 3 | 1 | 0 | 1 |
+------+------+-------+-------------------+--------------------+----------------------------+
7 rows in set
obclient> SELECT CONNECT_BY_ROOT col1, TO_NUMBER(CONNECT_BY_ROOT col1),
TO_CHAR(CONNECT_BY_ROOT col1), CONNECT_BY_ROOT(col1 + col2),
TO_NUMBER(CONNECT_BY_ROOT (col1 + col2)), TO_CHAR(CONNECT_BY_ROOT (col1 + col2))
FROM tbl1 START WITH col1 = 1 CONNECT BY NOCYCLE PRIOR col1 = col2;
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| CONNECT_BY_ROOTCOL1 | TO_NUMBER(CONNECT_BY_ROOTCOL1) | TO_CHAR(CONNECT_BY_ROOTCOL1) | CONNECT_BY_ROOT(COL1+COL2) | TO_NUMBER(CONNECT_BY_ROOT(COL1+COL2)) | TO_CHAR(CONNECT_BY_ROOT(COL1+COL2)) |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
7 rows in set
