在关系型数据库中存储树形结构数据时，通常采用自关联的方式设计表结构，通过父节点ID字段关联上级节点。要查询这类层级数据，递归CTE和CONNECT BY是两种最常用的SQL实现方案，不同数据库对这两种语法的支持情况存在差异。
两种语法的数据库支持情况
递归CTE是SQL标准语法，主流数据库如MySQL 8.0+、PostgreSQL、SQL Server、Oracle都支持该语法。CONNECT BY是Oracle最早推出的私有语法，目前仅Oracle和部分兼容Oracle的数据库支持，使用时需要注意数据库的适配性。
递归CTE的使用方法
递归CTE由两部分组成：锚定成员和递归成员，通过WITH RECURSIVE关键字定义。以下以组织架构表为例演示用法，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | INT | 节点ID |
| name | VARCHAR(50) | 节点名称 |
| parent_id | INT | 父节点ID，根节点为NULL |
查询某个节点的所有子节点
以下示例查询id为1的节点及其所有下属子节点：
WITH RECURSIVE sub_tree AS (
-- 锚定成员：查询根节点
SELECT id, name, parent_id, 1 AS level
FROM org_structure
WHERE id = 1
UNION ALL
-- 递归成员：关联子节点
SELECT o.id, o.name, o.parent_id, st.level + 1
FROM org_structure o
INNER JOIN sub_tree st ON o.parent_id = st.id
)
SELECT id, name, parent_id, level FROM sub_tree ORDER BY level, id;
查询某个节点的所有父节点
要查询指定节点的完整上级链路，只需要调整递归的关联方向即可：
WITH RECURSIVE parent_tree AS (
-- 锚定成员：查询目标节点
SELECT id, name, parent_id, 1 AS level
FROM org_structure
WHERE id = 5
UNION ALL
-- 递归成员：关联父节点
SELECT o.id, o.name, o.parent_id, pt.level + 1
FROM org_structure o
INNER JOIN parent_tree pt ON o.id = pt.parent_id
)
SELECT id, name, parent_id, level FROM parent_tree ORDER BY level DESC;
CONNECT BY的使用方法
CONNECT BY语法主要在Oracle数据库中使用，核心是通过CONNECT BY PRIOR指定父子关系的关联方向，START WITH指定查询的起始节点。
查询某个节点的所有子节点
同样查询id为1的节点及其所有子节点，Oracle语法实现如下：
SELECT id, name, parent_id, LEVEL FROM org_structure START WITH id = 1 CONNECT BY PRIOR id = parent_id ORDER BY LEVEL, id;
其中PRIOR放在id前表示当前行的id是子节点的parent_id，即向下查询子节点；如果要向上查询父节点，只需要调整PRIOR的位置即可。
查询某个节点的所有父节点
查询id为5的节点的所有上级父节点：
SELECT id, name, parent_id, LEVEL FROM org_structure START WITH id = 5 CONNECT BY PRIOR parent_id = id ORDER BY LEVEL DESC;
两种方案的对比与注意事项
- 兼容性：递归CTE符合SQL标准，通用性更强，新项目优先选择；如果是维护Oracle旧系统，可使用CONNECT BY减少适配成本。
- 性能：两种方案在层级深度较小时性能差异不大，层级过深时都需要注意递归深度限制，避免无限递归。
- 额外功能：CONNECT BY原生支持LEVEL伪列直接获取节点层级，递归CTE需要手动定义层级字段；CONNECT BY还可以通过SYS_CONNECT_BY_PATH函数直接拼接节点路径，递归CTE需要额外处理拼接逻辑。
使用递归CTE时需要注意，MySQL默认递归深度限制为1000，超过会报错，可通过设置cte_max_recursion_depth参数调整；Oracle的CONNECT BY默认递归深度限制为100，可通过CONNECT BY NOCYCLE避免循环引用导致的报错。
SQL递归CTECONNECT_BY树形结构查询 hierarchical_query修改时间：2026-06-24 22:24:33