在数据库的实际应用场景中，层级结构数据的查询是非常常见的需求，比如查询某个部门下所有的子部门、查询某个分类下所有的子分类、查询某个节点的所有父节点等，这类需求无法通过普通的单表查询直接实现，需要借助SQL的递归查询能力来完成。
什么是SQL递归查询
SQL递归查询指的是在查询过程中，查询结果会不断引用自身的前序结果，直到满足终止条件为止的查询方式。它主要用来处理具有层级关系、父子关系的数据，核心逻辑是先查询初始的基础数据，再基于基础数据不断向下或者向上扩展查询关联数据。
通用实现方式：CTE递归查询
CTE即公用表表达式，是SQL标准中定义的递归查询实现方式，目前MySQL 8.0+、PostgreSQL、SQL Server等主流数据库都支持这种语法。CTE递归查询分为两个部分：锚定成员和递归成员。
CTE递归查询语法结构
基本的CTE递归查询语法如下：
-- 定义递归CTE
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- 锚定成员：查询初始的基础数据
SELECT column1, column2, ...
FROM table_name
WHERE 初始条件
UNION ALL
-- 递归成员：基于前序结果查询下一级数据
SELECT t.column1, t.column2, ...
FROM table_name t
INNER JOIN cte_name c ON t.parent_id = c.id -- 关联条件根据层级关系定义
)
-- 查询CTE结果
SELECT * FROM cte_name;
实战示例：查询部门下所有子部门
假设有一张部门表dept，结构如下：
| id | dept_name | parent_id |
|---|---|---|
| 1 | 总公司 | NULL |
| 2 | 技术部 | 1 |
| 3 | 前端组 | 2 |
| 4 | 后端组 | 2 |
| 5 | 市场部 | 1 |
如果要查询总公司下所有的子部门，包括直接和间接下属的部门，可以使用以下CTE递归查询语句：
WITH RECURSIVE dept_cte (id, dept_name, parent_id, depth) AS (
-- 锚定成员：查询总公司作为初始节点
SELECT id, dept_name, parent_id, 0 AS depth
FROM dept
WHERE id = 1
UNION ALL
-- 递归成员：关联查询下一级部门
SELECT d.id, d.dept_name, d.parent_id, c.depth + 1
FROM dept d
INNER JOIN dept_cte c ON d.parent_id = c.id
)
SELECT id, dept_name, parent_id, depth FROM dept_cte;
执行上述语句后，会得到总公司以及所有下属部门的结果，depth字段表示部门所在的层级深度，0代表总公司，1代表一级子部门，2代表二级子部门。
Oracle数据库递归查询实现
Oracle数据库除了支持CTE递归查询之外，还提供了特有的connect by语法来实现递归查询，这种方式在Oracle生态中使用非常广泛。
connect by语法结构
基本的connect by语法如下：
SELECT column1, column2, ... FROM table_name START WITH 初始条件 -- 定义初始查询节点 CONNECT BY PRIOR 子节点列 = 父节点列 -- 定义层级关联关系，PRIOR放在子节点列前表示向下递归，放在父节点列前表示向上递归
实战示例：查询部门层级数据
同样使用上面的dept表结构，要查询总公司下所有子部门，使用connect by语法的实现如下：
SELECT id, dept_name, parent_id, LEVEL AS depth FROM dept START WITH id = 1 -- 初始节点为总公司 CONNECT BY PRIOR id = parent_id -- 向下递归，PRIOR id表示当前节点的id是下一级节点的父id
如果要查询前端组的所有上级部门，只需要调整PRIOR的位置即可：
SELECT id, dept_name, parent_id, LEVEL AS depth FROM dept START WITH id = 3 -- 初始节点为前端组 CONNECT BY PRIOR parent_id = id -- 向上递归，PRIOR parent_id表示当前节点的父id是上一级节点的id
递归查询的注意事项
- 递归查询必须有明确的终止条件，避免出现无限递归的情况，比如数据中存在父子节点的循环引用时，需要添加过滤条件防止死循环。
- CTE递归查询中，锚定成员和递归成员的列数量和数据类型必须保持一致，否则会执行报错。
- 不同数据库的递归查询性能存在差异，在处理大数据量的层级数据时，建议提前对关联字段添加索引，提升查询效率。
- Oracle的connect by语法中，LEVEL是伪列，用来表示当前节点的层级深度，不需要额外定义。
总结
SQL递归查询是处理层级数据的核心能力，不同数据库的实现语法略有不同。CTE递归查询是通用性更强的方式，适合跨数据库场景使用；Oracle的connect by语法是Oracle特有的实现，语法更简洁。开发者可以根据自己使用的数据库类型，选择合适的递归查询实现方式，快速完成层级数据的查询需求。
SQL递归查询CTE层级数据查询connect_by修改时间：2026-07-02 21:39:21