SQL 使用 CTE 重构复杂查询

CTE 更易读因将逻辑拆为命名步骤，避免嵌套右滑；建议分层建CTE（清洗/计算/汇总），每段≤20行，禁用无OFFSET/FETCH的ORDER BY，多次引用大结果宜用临时表。

CTE 为什么比嵌套子查询更易读

因为 CTE 把逻辑拆成命名步骤，避免了 FROM (SELECT ... FROM (SELECT ...)) 这种向右滑动的嵌套。数据库执行计划未必优化，但人眼阅读成本显著下降。

常见错误是把所有中间结果都塞进一个 CTE 里，反而让 WITH 块过长。建议按「数据清洗」「业务计算」「聚合汇总」分层建 CTE，每段不超过 20 行。

• 别在 CTE 里写 ORDER BY（除配合 OFFSET/FETCH 外），它不保证最终结果顺序
• CTE 不是视图，每次被引用都会重新执行——如果同一个 CTE 被多次引用，且数据量大，考虑用临时表替代
• PostgreSQL 支持递归 CTE，SQL Server 和 Oracle 也支持，但 MySQL 8.0+ 才支持；低于该版本会直接报错 ERROR 1142: SELECT command denied（实际是语法不识别）

如何用 CTE 替换多层 JOIN + 子查询

典型场景：查每个部门薪资前 3 的员工，还要带入部门平均薪资和公司总人数。原来可能要三层子查询嵌套 + 多个 JOIN，现在可以拆解：

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_dept_salary
  FROM employees
  GROUP BY dept_id
),
company_total AS (
  SELECT COUNT(*) AS total_employees
  FROM employees
),
ranked_emps AS (
  SELECT 
    e.*,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees e
)
SELECT 
  r.name,
  r.dept_id,
  r.salary,
  d.avg_dept_salary,
  c.total_employees
FROM ranked_emps r
JOIN dept_avg d ON r.dept_id = d.dept_id
CROSS JOIN company_total c
WHERE r.rn <= 3;

注意：CROSS JOIN company_total 比 (SELECT total_employees FROM company_total) 在可读性和维护性上更清晰，且多数引擎能正确优化。

CTE 中引用自身（递归）的硬约束

递归 CTE 必须包含两个部分：锚点（anchor）和递归成员（recursive member），且递归成员只能引用 CTE 自身一次，不能出现在 JOIN 右侧或子查询中。

常见报错：Recursive common table expression 'tree' does not contain a recursive member，通常是因为：

• 漏写 UNION ALL（不能用 UNION）
• 递归查询里用了聚合函数（如 COUNT）、GROUP BY 或 TOP
• 锚点和递归部分的列数、类型不一致（例如锚点返回 INT，递归返回 VARCHAR）

MySQL 8.0+ 要求必须显式声明 MAXRECURSION 限制（通过 SET cte_max_recursion_depth = 1000），否则默认只跑 100 层，超限报错 ERROR 3636: Recursive query aborted after 100 iterations。

性能陷阱：CTE 不是物化视图

很多人误以为 CTE 会缓存结果，其实绝大多数数据库（包括 PostgreSQL 14、SQL Server 2025）默认按需重算。比如下面这段：

WITH base AS (SELECT * FROM huge_table WHERE status = 'active')
SELECT COUNT(*) FROM base
UNION ALL
SELECT SUM(amount) FROM base;

实际执行时，base 可能被扫描两次。PostgreSQL 有 MATERIALIZED 关键字（v12+），可显式要求物化：

WITH base AS MATERIALIZED (SELECT * FROM huge_table W
HERE status = 'active')

但 SQL Server 和 MySQL 没有等效语法，此时应改用临时表或表变量。

真正需要警惕的是：当 CTE 包含非确定性函数（如 GETDATE()、NEWID()）时，每次引用都可能产生不同结果——这在调试时极难发现。