多层嵌套子查询是SQL从入门到进阶路上最常见的“烂代码”重灾区。一个查询里出现三四个IN (SELECT ...)或者EXISTS还勉强能看懂,但如果业务逻辑涉及层级关系、连续过滤、多重聚合,子查询往往会像俄罗斯套娃一样越包越深。最后写出来的SQL自己隔一周都看不懂,更别说交接给同事。这篇文章会用几个具体案例说明,如何借助WITH子句(通用表表达式,CTE)将嵌套结构拍平,并重点演示递归CTE在树形数据查询中的威力。

多层嵌套子查询的痛点到底在哪
假设有一张员工表employee,包含id、name、manager_id三个字段,其中manager_id指向自己的上级。如果要查出“张三的下属的下属”,一种常见的写法是嵌套子查询:先找到张三的直接下属,再把这些下属的id作为条件去查他们的下属。代码大致如下:
SELECT id, name, manager_id
FROM employee
WHERE manager_id IN (
SELECT id
FROM employee
WHERE manager_id = (
SELECT id
FROM employee
WHERE name = '张三'
)
);
这段逻辑只有三层,读起来还算凑合。但需求稍微扩展一下——比如要查出张三的所有层级下属(不论多少级)、或者要同时统计每个分支的人数——子查询就会急剧膨胀。更麻烦的是,SQL的书写顺序和执行顺序不一致,嵌套子查询从内向外阅读,但业务逻辑往往是从外向内的,这种割裂感会极大增加理解成本。调试时,你不得不把最内层的子查询单独拿出来跑一遍,再一层层往外验证,效率极低。
另一个隐性问题是索引失效。多层IN子查询有时会迫使优化器选择对每行都执行一次子查询,尤其是关联字段类型不一致或者缺少合适复合索引时,查询计划可能退化到接近O(n²)。即便逻辑上正确,性能也可能拖垮整个接口。所以遇到嵌套超过两层的SQL,第一反应应该是重构,而非继续往上叠条件。
用普通CTE拆解复杂查询
WITH子句(Common Table Expression,通用表表达式)允许我们在一条SQL语句的开头定义一个或多个临时的命名结果集,后续的主查询可以像使用普通表一样引用它们。它的语法非常直观:
WITH 临时表名 AS (
子查询语句
)
SELECT ...
FROM 临时表名;
同一个WITH中可以定义多个CTE,用逗号分隔,后面的CTE可以引用前面已经定义好的CTE。这种“递进式定义”使得复杂查询可以按照业务逻辑的先后顺序一步步拆解,每一层只完成一个简单的过滤或聚合,最后组合起来。上文中查询“张三的下属的下属”可以改写成:
WITH direct_sub AS (
SELECT id, name
FROM employee
WHERE manager_id = (SELECT id FROM employee WHERE name = '张三')
),
second_level_sub AS (
SELECT e.id, e.name, e.manager_id
FROM employee e
JOIN direct_sub d ON e.manager_id = d.id
)
SELECT id, name, manager_id
FROM second_level_sub;
这个改写版本虽然代码行数变多了,但阅读顺序和业务思路完全一致:先找到直接下属,再通过连接找到下一级。如果后续还要加上第三级、第四级,只需要在WITH中继续追加CTE即可,不会出现括号嵌套地狱。更关键的是,每个CTE都可以单独拿出来验证,调试成本大幅降低。
此外,普通CTE还能显著提升SQL的可维护性。当同一个子查询在多个地方被引用时,把它提取成CTE可以避免重复编写相同逻辑,也方便统一修改。例如多个报表都需要用到“近30天活跃用户”这个过滤条件,直接定义一个CTE,后续所有查询引用同一个名字,比在每个WHERE里复制粘贴子查询要清晰得多。
递归CTE处理层级数据的正确姿势
普通的CTE只能做一次性拆解,对于“找出某个节点下所有层级的子节点”这种树形遍历需求,就需要用到递归CTE。不同数据库的语法略有差异,标准SQL中是在WITH后面加上RECURSIVE关键字,MySQL 8.0、PostgreSQL、SQLite都支持。递归CTE由两部分组成:锚定成员(初始查询)和递归成员(引用CTE自身的查询),两部分用UNION ALL连接。
以员工组织架构为例,要求返回“张三”及其所有层级的直接和间接下属。递归CTE写法如下:
WITH RECURSIVE emp_tree AS (
-- 锚定成员:起点员工
SELECT id, name, manager_id, 1 AS level
FROM employee
WHERE name = '张三'
UNION ALL
-- 递归成员:逐层向下查找
SELECT e.id, e.name, e.manager_id, et.level + 1
FROM employee e
INNER JOIN emp_tree et ON e.manager_id = et.id
)
SELECT id, name, manager_id, level
FROM emp_tree
ORDER BY level, id;
这段代码的执行过程可以理解为:锚定成员先找出张三本人,然后递归成员拿当前emp_tree中的每一行去employee表里找manager_id等于该行id的人,找到后加入结果集,并继续下一轮递归,直到没有新的记录产生为止。level字段用来标识层级深度,方便后续筛选或排序。
对比原来的多层嵌套写法,递归CTE的代码结构非常稳定:不管层级有多深,代码行数都不会增加,因为递归是通过自我引用实现的。如果树形数据有10层,用嵌套子查询几乎没法写,但递归CTE只需要修改锚定成员里的起点条件即可。这正是CTE递归在处理组织架构、分类目录、评论回复、权限继承等场景时不可替代的优势。
使用CTE递归的性能考量与常见陷阱
递归CTE虽然提升了可读性,但并非没有代价。数据库执行递归CTE时,本质上是循环扫描表,每次递归都要和employee表做一次连接。如果数据量大且层级很深,递归次数可能很多,性能会比专门的图数据库或预先存储的路径枚举方案差不少。因此在使用递归CTE前,务必确保递归列上有合适的索引。上面的例子中,employee.manager_id必须建立索引,否则每一层递归都要全表扫描,效率急剧下降。
另一个必须注意的问题是递归深度限制。大多数数据库默认限制递归层级(例如MySQL默认1000层,PostgreSQL默认没有硬限制但受栈深度影响)。如果实际数据层级超过限制,查询会报错。可以通过参数调整(如MySQL的cte_max_recursion_depth),但更稳妥的做法是在递归成员中增加循环检测条件,避免出现自引用环导致无限递归。例如在递归部分加上WHERE e.id <> et.id或者使用数组记录已访问节点。
还有一点:递归CTE的UNION ALL不能改成UNION,因为UNION会去重,而层级遍历中需要保留所有重复行(同一员工可能通过不同路径被多次遍历的情况虽然少见,但去重会改变语义)。此外,递归成员的WHERE条件不能直接引用聚合函数或外部的非锚定表,否则会破坏递归的终止条件。最后强调,改写嵌套子查询并不代表完全抛弃子查询——一些简单的EXISTS或IN子查询在优化器处理下效率很高,盲目把所有子查询都换成CTE反而会增加解析开销。判断标准很简单:只要嵌套超过两层、或者逻辑包含明显的分步计算,就优先考虑CTE。