CTE使用场景答疑：掌握CTE编写后该在哪些场景使用

CTE（公共表表达式）的核心作用是在单次SQL查询中定义可复用的临时结果集，不需要持久化存储数据库对象，以下是实际开发中最适合用CTE的几类场景：

• 拆分多层嵌套子查询，降低逻辑理解成本
  写复杂查询时如果嵌套3层以上子查询，代码可读性会极差，调试时需要从最内层括号往外逐层捋逻辑，改需求时很容易改漏条件。用CTE可以把每一步的中间计算逻辑按业务执行顺序单独定义，整个SQL的阅读逻辑是线性的，和业务思考路径完全一致，后续维护调整时只需要修改对应步骤的CTE逻辑即可，不需要在多层嵌套括号里来回跳转。
• 同一中间结果需要在单条查询中多次引用
  做统计报表类需求时，经常会出现多个指标计算都依赖同一个基础筛选结果的情况：比如同时计算某类用户的订单总量、平均客单价、退款率，三个指标都需要先筛选出符合规则的目标用户群体。如果不用CTE，同一段用户筛选逻辑要重复写3次，不仅代码冗余，后续调整筛选规则（比如把“注册满30天”改成“注册满60天”）时，漏改任意一处就会导致数据错误。用CTE只需要定义一次基础结果集，后续所有计算直接引用即可，能大幅减少重复代码和出错概率。

  注意：多数数据库的CTE不会默认做结果缓存，这里的复用仅指逻辑层面减少重复代码，不要为了所谓“提升性能”的误区硬套CTE，部分场景下CTE的执行效率甚至不如写重复子查询。

• 实现递归层级查询
  这是CTE独有的不可替代的场景，普通子查询、关联查询都很难简洁实现这类需求。常见的递归场景包括：查询组织架构下某员工的所有多级下属、遍历多级商品类目树、拉取评论区的盖楼回复链路、计算累计值等。递归CTE通过「锚点成员（定义起始节点/初始值）+ 递归成员（关联CTE本身迭代计算下一层结果）」的固定结构，不需要写存储过程或者自定义循环，几行代码就能处理任意深度的层级数据，示例代码如下：

  WITH RECURSIVE dept_structure AS (
      -- 锚点：查询所有一级部门作为层级起点
      SELECT id, dept_name, parent_id, 1 AS dept_level
      FROM department
      WHERE parent_id = 0
      UNION ALL
      -- 递归：关联上一层部门结果，查询下一级子部门
      SELECT d.id, d.dept_name, d.parent_id, ds.dept_level + 1 AS dept_level
      FROM department d
      INNER JOIN dept_structure ds ON d.parent_id = ds.id
  )
  SELECT * FROM dept_structure;
  

• 轻量一次性查询的预处理，替代临时表/视图
  如果只是临时跑一次分析查询，没必要专门在数据库里创建持久化视图，也不需要手动创建临时表、写入数据、执行完再删除。CTE的生命周期仅在单次查询内，查询执行完成后自动销毁，不会残留冗余数据库对象，尤其适合没有建表、建视图权限的开发/分析人员做临时复杂查询的逻辑拆分。

补充提醒：不要为了用CTE而用CTE，如果是简单的单表过滤、两表关联查询，直接写基础逻辑即可，硬套CTE反而会增加无意义的代码层级。

内容的提问来源于stack exchange，提问作者Attitude Black