cte适用于复用、调试或树形结构，子查询适合单次过滤；cte是否只执行一次取决于数据库：postgresql默认物化，mysql/sql server可能内联展开；相关子查询是性能黑洞，cte无法替代，应改用join或窗口函数；递归查询必须用with recursive。
CTE 和子查询都能生成临时结果集，但它们在行为、可维护性和执行逻辑上根本不是一回事。直接说结论：**需要复用、调试或处理树形结构时必须用 CTE；简单单次过滤或关联，子查询更轻量，且某些场景下性能反而更好**。
CTE 被多次引用时真的只执行一次吗？
不一定——取决于数据库引擎。PostgreSQL 默认物化 CTE，即计算一次、缓存结果、后续引用直接读缓存；但 MySQL 8.0+ 和 SQL Server 的优化器可能选择“内联展开”，也就是把 CTE 再次嵌入到每个引用位置，实际执行多次。
- 验证方法：对含大表扫描的
CTE执行EXPLAIN ANALYZE，看执行计划里CTE Scan出现几次 - 强制物化（PostgreSQL）：加
MATERIALIZED关键字，如WITH cte AS MATERIALIZED (SELECT ...) - 避免意外重复执行：如果
CTE里有耗时操作（比如JOIN千万级表），又在主查询中被引用 ≥2 次，先确认目标数据库是否真物化，否则不如改用临时表
子查询在 WHERE 中触发“相关子查询”有多危险？
相关子查询是性能黑洞，尤其在 WHERE 或 SELECT 列中引用外层字段时，比如：(SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = u.id)。这种写法会让数据库为外层每一行都执行一遍内层查询。
- 常见错误现象：外表 10 万行 → 内层查询执行 10 万次 → 查询从 200ms 崩到 40s
-
CTE无法替代相关子查询：因为CTE是预计算，不感知外层行值；想改写，得用LEFT JOIN + GROUP BY或窗口函数COUNT() OVER (PARTITION BY user_id) - 能用窗口函数就别硬套
CTE：比如“查每个用户的最新订单”，ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)比CTE+ 自连接高效得多
递归查询只能靠 WITH RECURSIVE，子查询完全做不到
组织架构、评论回复链、BOM 物料清单这类树形/层级数据，子查询语法根本不支持递归；强行用自连接最多撑 3–4 层，再深就写不下去了。
-
WITH RECURSIVE必须包含两部分：锚点查询（根节点）和递归成员（子节点），且递归成员必须JOIN到自身 CTE 名 - 容易踩的坑：
UNION ALL是必须的（UNION会去重并阻断递归），且递归查询里不能出现聚合、GROUP BY、ORDER BY - 防无限循环：多数数据库默认限制递归深度（如 PostgreSQL 是 100 层），可通过
max_recursion_depth（MySQL）或search_path（PostgreSQL）调整，但应优先在WHERE中加层级控制，比如level
active_orders_30d 的 CTE，删掉它，整个报表逻辑就断得明明白白。
