在SQL开发过程中，很多查询场景需要多次使用相同的子查询结果，如果每次都重复编写子查询，不仅会让代码变得冗长，后续修改逻辑时还需要逐个调整所有重复的部分，很容易出现遗漏。CTE也就是公用表表达式，能够把一段子查询逻辑定义为临时的结果集，在后续的查询中多次引用，有效提升代码的复用性。
CTE的基本语法
CTE的语法结构非常简单，使用WITH关键字开头，后面跟着CTE的名称和对应的查询逻辑，定义完成后可以在主查询中像使用普通表一样引用它。基本语法格式如下：
-- 定义单个CTE
WITH cte_name AS (
-- 子查询逻辑
SELECT column1, column2 FROM table_name WHERE condition
)
-- 主查询引用CTE
SELECT * FROM cte_name;
如果需要定义多个CTE，只需要在第一个CTE之后用逗号分隔，继续编写后续的CTE即可，所有CTE定义完成后统一编写主查询。
-- 定义多个CTE
WITH cte1 AS (
SELECT id, name FROM user_table WHERE status = 1
),
cte2 AS (
SELECT user_id, order_count FROM order_table GROUP BY user_id
)
-- 关联两个CTE查询
SELECT cte1.name, cte2.order_count
FROM cte1
JOIN cte2 ON cte1.id = cte2.user_id;
利用CTE复用子查询的实际场景
场景一：多次引用相同统计逻辑
假设我们需要查询每个部门的员工人数，同时筛选出员工人数大于10的部门，并且还要展示这些部门的平均工资。如果没有CTE，我们需要两次编写统计部门员工人数的子查询，代码冗余度很高。
使用CTE的话，只需要把部门员工人数的统计逻辑定义为CTE，后续两次引用即可：
WITH dept_emp_count AS (
-- 统计每个部门的员工人数
SELECT dept_id, COUNT(*) AS emp_num
FROM employee
GROUP BY dept_id
)
-- 主查询中两次引用CTE
SELECT
d.dept_name,
dec.emp_num,
AVG(e.salary) AS avg_salary
FROM dept_emp_count dec
JOIN department d ON dec.dept_id = d.id
JOIN employee e ON dec.dept_id = e.dept_id
-- 引用CTE的结果做筛选
WHERE dec.emp_num > 10
GROUP BY d.dept_name, dec.emp_num;
场景二：递归查询中的逻辑复用
CTE还支持递归查询，适合处理层级结构的数据，比如组织架构、分类树等。递归CTE分为锚定部分和递归部分，整个逻辑封装在CTE中，后续如果需要调整层级查询的规则，只需要修改CTE内部即可，不需要改动其他引用部分。
以下是查询员工上下级层级的示例：
WITH RECURSIVE emp_hierarchy AS (
-- 锚定部分：查询最顶层的员工（没有上级的员工）
SELECT id, name, manager_id, 1 AS level
FROM employee
WHERE manager_id IS NULL
UNION ALL
-- 递归部分：关联下级员工
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
-- 引用递归CTE查询所有层级的员工
SELECT * FROM emp_hierarchy ORDER BY level, id;
CTE和传统子查询的对比
很多人会疑惑，既然子查询也能实现类似的逻辑，为什么要用CTE？我们可以通过下面的对比看出CTE的优势：
| 对比维度 | 传统子查询 | CTE |
|---|---|---|
| 代码复用性 | 相同逻辑需要重复编写，复用性差 | 定义一次可多次引用，复用性强 |
| 可读性 | 嵌套层级多时逻辑混乱，难以阅读 | 逻辑拆分清晰，命名直观，可读性强 |
| 维护成本 | 修改逻辑需要逐个调整所有重复子查询 | 只需要修改CTE定义部分即可 |
| 递归支持 | 不支持递归查询 | 支持递归查询，适合层级数据处理 |
使用CTE的注意事项
- CTE定义的结果集只在当前查询执行期间有效，查询结束后就会释放，不会像临时表一样持久化存储。
- CTE的名称需要和当前查询中的其他表名、别名区分开，避免命名冲突。
- 递归CTE需要设置合理的终止条件，避免出现无限递归导致查询报错。
- 部分数据库对CTE的支持版本有要求，比如MySQL需要8.0及以上版本才支持CTE，使用前需要确认数据库版本。
通过合理使用CTE，我们可以把重复的SQL子查询逻辑封装起来，减少冗余代码，让整个查询的结构更清晰，后续维护时也能降低出错的概率，是提升SQL代码复用性的有效手段。