--- title: github-fill description: 层次查询伪列 层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 CONNECT BY 子句。本文主要介绍层次查询伪列的三种类型的使用与说明，包括 CONNECT_BY_ISCYCLE 伪列、CONNECT_BY_ISLEAF 伪列和 LEVEL 伪列。 CONNECT_BY_ISCYCLE 伪列 … --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.5.0 # 层次查询伪列 更新时间：2026-04-10 14:31:27 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.5.0/zh-CN/700.reference/500.sql-reference/100.sql-syntax/300.common-tenant-of-oracle-mode/200.pseudo-column-of-oracle-mode/200.hierarchical-query-pseudo-columns-of-oracle-mode.md) 层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 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。 ## 层次查询示例 ```sql 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 ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线