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 2022）默认按需重算。比如下面这段：
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 WHERE status = 'active')
但 SQL Server 和 MySQL 没有等效语法，此时应改用临时表或表变量。
真正需要警惕的是：当 CTE 包含非确定性函数（如 GETDATE()、NEWID()）时，每次引用都可能产生不同结果——这在调试时极难发现。
