CTE全称为公用表表达式，是SQL标准中定义的一种临时命名结果集，它只在当前查询的执行周期内有效，不需要像临时表那样额外创建和销毁，能有效简化复杂查询的编写逻辑。CTE的核心作用是将复杂的子查询拆分，提升SQL语句的可读性和可维护性。
一、CTE的基础原理
CTE的定义使用WITH关键字，语法结构为WITH cte_name (column_list) AS (cte_query)，其中cte_name是CTE的名称，column_list是可选的列名定义，cte_query是生成CTE结果集的查询语句。
从执行逻辑来看，CTE并不是提前生成并存储的临时结果集，而是在查询执行时，将CTE的定义内联到引用它的查询中，相当于把CTE的查询逻辑替换到对应的引用位置。不过部分数据库引擎会对CTE做优化，比如多次引用同一个CTE时，可能会缓存其执行结果避免重复计算。
普通CTE的执行示例：
-- 定义CTE查询员工表中部门ID为10的员工信息
WITH dept_10_employees AS (
SELECT id, name, salary
FROM employees
WHERE dept_id = 10
)
-- 引用CTE查询薪资大于5000的员工
SELECT id, name, salary
FROM dept_10_employees
WHERE salary > 5000;
二、递归CTE的构成与实现方式
递归CTE是CTE的特殊形式，用于处理具有层级、递归特性的数据，比如组织架构、分类树、路径遍历等场景。递归CTE由两部分组成：锚定成员和递归成员，两部分通过UNION ALL连接。
1. 递归CTE的结构说明
- 锚定成员：是递归的起始点，返回递归的初始结果集，这部分查询不包含对CTE自身的引用，是普通的非递归查询。
- 递归成员：引用CTE自身的查询，基于锚定成员返回的结果集进行迭代计算，每次迭代都会将新的结果集和之前的结果集合并，直到没有新的行产生为止。
- 终止条件：递归成员的执行会不断产生新行，当递归成员查询返回空结果集时，递归自动终止，避免无限循环。
2. 递归CTE的执行流程
- 先执行锚定成员的查询，得到初始结果集，将其作为当前结果集。
- 执行递归成员的查询，引用当前的CTE结果集，得到新的结果集。
- 将新的结果集通过
UNION ALL合并到总结果集中，同时把新结果集作为下一轮迭代的当前结果集。 - 重复步骤2和3，直到递归成员返回空结果集，停止递归，返回最终的总结果集。
3. 递归查询实现示例
假设有一张员工表employees，包含id（员工ID）、name（员工姓名）、manager_id（直属上级ID）字段，现在需要查询员工ID为1的所有下属（包括间接下属），可以使用递归CTE实现：
-- 递归CTE查询员工1的所有下属
WITH RECURSIVE sub_employees AS (
-- 锚定成员：查询员工1的直接下属
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id = 1
UNION ALL
-- 递归成员：查询下属的下属，层级加1
SELECT e.id, e.name, e.manager_id, se.level + 1
FROM employees e
INNER JOIN sub_employees se ON e.manager_id = se.id
)
-- 查询所有下属信息
SELECT id, name, manager_id, level
FROM sub_employees
ORDER BY level, id;
上述代码中，WITH RECURSIVE是定义递归CTE的关键字，不同数据库可能有差异，比如SQL Server不需要加RECURSIVE关键字，MySQL 8.0及以上版本需要加该关键字。锚定成员先找到员工1的直接下属，递归成员不断关联下属的下属，直到没有更下层的员工为止。
三、递归CTE使用注意事项
- 递归成员中不能使用
GROUP BY、HAVING、聚合函数等操作，因为这些操作会打乱迭代的逻辑。 - 要避免无限递归，比如数据中存在循环的上下级关系时，递归会一直执行，可以在递归成员中添加层级限制，比如
WHERE se.level < 10控制最大递归深度。 - 不同数据库对递归CTE的支持程度不同，编写时需要参考对应数据库的官方文档，确认语法细节。
递归CTE是处理层级数据的有效工具，相比传统的自连接查询，它能更清晰地表达递归逻辑，也更容易控制递归的终止条件，适合在树形结构、路径查询等场景中使用。