在SQL的实际开发中，当业务需求需要关联多张表、做多次聚合或者过滤时，很容易写出多层嵌套的查询语句。随着嵌套层级的增加，代码会变得越来越臃肿，不仅阅读起来费劲，后续修改逻辑也很容易出错。
多层嵌套查询的痛点
我们先看一个常见的多层嵌套查询场景，需求是查询每个部门中薪资高于部门平均薪资的员工信息，同时关联部门名称。传统的嵌套查询写法如下：
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name
FROM
(SELECT
dept_id,
AVG(salary) AS avg_salary
FROM
employee
GROUP BY
dept_id) AS dept_avg
JOIN
employee e ON dept_avg.dept_id = e.dept_id
JOIN
department d ON e.dept_id = d.dept_id
WHERE
e.salary > dept_avg.avg_salary;
这个查询只有两层嵌套，逻辑已经需要反复跳转查看。如果业务再复杂一些，比如需要先过滤入职满一年的员工，再计算部门平均薪资，再关联其他业务表，嵌套层级可能会达到三四层甚至更多，这时候代码的维护成本会急剧上升。
CTE的基本用法
CTE全称是公用表表达式，英文是Common Table Expression，它可以通过WITH关键字定义临时结果集，这些结果集只在当前查询中有效，不会存储在数据库中。CTE的基本语法如下：
WITH cte_name (column1, column2, ...) AS (
-- 这里写CTE的查询逻辑
SELECT column1, column2 FROM table_name WHERE condition
)
-- 使用CTE进行后续查询
SELECT * FROM cte_name;
CTE的优势在于可以把复杂的嵌套逻辑拆分成多个独立的、有语义名称的临时结果集，每个结果集的逻辑都很清晰，后续查询直接引用这些结果集即可，不需要反复嵌套子查询。
用CTE简化多层嵌套查询
我们用CTE改写前面查询部门高薪员工的案例，改写后的代码如下：
-- 定义部门平均薪资的CTE
WITH dept_avg_salary AS (
SELECT
dept_id,
AVG(salary) AS avg_salary
FROM
employee
GROUP BY
dept_id
)
-- 关联员工表和部门表查询最终结果
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name
FROM
employee e
JOIN
dept_avg_salary das ON e.dept_id = das.dept_id
JOIN
department d ON e.dept_id = d.dept_id
WHERE
e.salary > das.avg_salary;
对比两种写法，CTE版本把计算部门平均薪资的逻辑单独拆成了一个名为dept_avg_salary的临时结果集，后续查询直接引用这个结果集即可。如果业务需要增加过滤条件，比如只统计入职时间超过1年的员工，只需要在CTE内部添加过滤条件，不需要改动后续的主查询逻辑，维护起来非常方便。
CTE的递归用法
除了简化普通嵌套查询，CTE还支持递归查询，非常适合处理层级结构的数据，比如组织架构、分类树等。递归CTE的语法如下：
WITH RECURSIVE cte_name AS (
-- 锚定部分：初始查询，返回层级的最顶层数据
SELECT column1, column2, 1 AS level
FROM table_name
WHERE parent_id IS NULL
UNION ALL
-- 递归部分：关联自身，查询下一层级数据
SELECT t.column1, t.column2, cte.level + 1
FROM table_name t
JOIN cte_name cte ON t.parent_id = cte.column1
)
SELECT * FROM cte_name;
递归CTE可以把原本需要用存储过程或者复杂嵌套查询实现的层级查询，用简洁的代码实现，逻辑清晰易懂。
使用CTE的注意事项
- CTE只在当前查询执行期间有效，不会像临时表一样持久化存储，执行完查询后就会释放。
- 不是所有数据库都支持递归CTE，比如MySQL从8.0版本才开始支持，使用之前需要确认数据库的版本和兼容性。
- CTE的结果集如果数据量很大，可能会影响查询性能，这时候需要结合索引等优化手段使用。
总的来说，CTE是简化SQL多层嵌套查询、提升代码可读性的有效工具，在实际开发中合理运用CTE，可以让SQL代码更清晰、更易维护，也能降低后续迭代修改的成本。