CTE 比子查询在多层过滤且需复用中间结果时可读性更高；如查各部门薪资前2名，子查询需三层嵌套致逻辑跳转频繁，CTE 则可拆解为清晰的排名与筛选两步。
CTE 和子查询在 WHERE 中嵌套时的可读性差异
当需要多层过滤、且中间结果要复用时，WITH 子句比嵌套 select 明显更易追踪。比如查「每个部门薪资前 2 名员工」，用子查询会写成三层嵌套：外层查员工，中层查部门最高薪，内层再算排名——逻辑跳转频繁，改一个条件就得通读三遍。
用 CTE 就能拆成清晰步骤：
WITH ranked AS ( SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees ) SELECT dept, name, salary FROM ranked WHERE rn <= 2;关键点：ranked 是具名中间结果，后续可直接引用；而子查询每次出现都要重写一遍 ROW_NUMBER() 表达式，不仅冗长，还容易漏改某一层的 PARTITION BY 或排序方向。
CTE 被多次引用时是否真能提升性能
不是所有数据库都把 CTE 当物化视图处理。postgresql 12+ 默认对 WITH 执行“内联展开”，即逻辑上等价于子查询，不会缓存中间结果；但加上 MATERIALIZED 关键字后才会真正执行一次并复用。
mysql 8.0 则默认物化（除非加 NOT MATERIALIZED），SQL Server 的 CTE 在多数情况下也按物化行为优化。
- PostgreSQL：用
WITH RECURSIVE或显式MATERIALIZED才保证只算一次 - MySQL：无需额外关键字，但若 CTE 引用超过一次且含聚合，仍可能重复计算（需看执行计划）
- SQL Server：CTE 本身不物化，但优化器常自动选择物化路径，建议用
OPTION (RECOMPILE)配合观察
别盲目相信“CTE 一定更快”——先看 EXPLAIN 或执行计划里 CTE 对应节点的执行次数。
递归 CTE 替代自连接的典型误用场景
查组织架构树形数据时，很多人用自连接 + 多层 LEFT JOIN 拼层级，最多支持固定深度（比如只到三级经理）。一旦要求“查出所有下属，不限层级”，就必须用递归 CTE。
常见错误是锚成员（anchor）和递归成员（recursive）的 union ALL 顺序写反，或遗漏终止条件导致无限循环：
WITH RECURSIVE org AS ( -- 锚：顶层领导 SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- 递归：下属 SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e INNER JOIN org o ON e.manager_id = o.id WHERE o.level < 10 -- 必须加深度限制防死循环 ) SELECT * FROM org;注意：WHERE o.level 不是可选项，某些数据库（如 PostgreSQL）不加会报错；SQL Server 要靠 MAXRECURSION 提示控制。
CTE 无法替代子查询的边界情况
有些子查询语义天然无法被 CTE 表达，比如相关子查询（correlated subquery）中依赖外部查询字段的场景：
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count FROM customers c;这个 o.customer_id = c.id 是跨作用域的关联，CTE 无法直接引用外部 c.id。强行改写只能用 JOIN + GROUP BY，但语义已变——如果某客户没订单，子查询返回 0，而 JOIN 默认丢弃该行，得补 LEFT JOIN 和 COALESCE。
所以 CTE 不是万能替代品：它擅长结构化中间计算，但不解决相关性问题。遇到带外部引用的子查询，优先考虑 JOIN 或窗口函数，而不是硬套 CTE。
真正难的是判断什么时候该拆、什么时候该合——比如聚合后又要过滤聚合结果，用 CTE 套一层 HAVING 逻辑就比在子查询里堆 CASE 更稳；但简单单表条件过滤，硬套 CTE 反而增加阅读负担。