cte不能替代简单group by子查询。它真正适用场景是复用聚合结果、递归查询或分步构建中间逻辑；硬套在单层分组上反而降低性能与可读性，且需显式声明列名、避免窗口别名误用于where。
CTE能替代子查询，但别硬套在简单GROUP BY上
CTE（WITH）不是万能分组加速器。它真正有用的地方，是当你需要复用同一组聚合结果多次、或分步构建中间状态时。比如先算每个用户的订单总额，再基于这个总额做分层统计（高价值/中价值/低价值用户数），这时候硬写嵌套子查询会非常难读，而CTE能让逻辑分层清晰。
常见错误是：把单层 GROUP BY 包进 WITH 里，纯粹为了“用CTE而用CTE”。这不仅没提升可读性，还可能让优化器放弃某些索引下推路径——尤其在 PostgreSQL 或 SQL Server 中，过度嵌套 CTE 可能导致物化（spooling），反而拖慢执行。
- 适用场景：
WITH后面要多次引用同一聚合结果；需要递归（如组织树）；逻辑必须按步骤拆解（如先过滤再聚合再关联） - 不适用场景：单次
SELECT ... FROM (...) t GROUP BY ...就能搞定的聚合 - 注意 MySQL 8.0+ 才原生支持 CTE；MySQL 5.7 或更低版本写
WITH会直接报错ERROR 1064
写多层CTE时，命名和字段顺序必须显式声明
CTE 的 AS 后括号里的列名列表，不是可选的装饰。一旦你在第一层 CTE 中用了 SELECT a+b AS total, COUNT(*) AS cnt，而没写 WITH user_summary(total, cnt) AS (...)，那么第二层 CTE 引用时就只能靠位置推断——这在字段增减或顺序调整后极易出错，而且不同数据库行为不一致（PostgreSQL 允许省略，SQL Server 要求显式）。
更隐蔽的问题是：如果某层 CTE 返回了重复列名（比如两个 JOIN 表都含 id 字段又没加别名），即使语法通过，后续引用 id 时会报 column reference "id" is ambiguous。
- 务必为每层 CTE 显式声明列名，例如：
WITH sales_by_month(month, revenue, order_count) AS (...) - 所有
JOIN中涉及同名列，必须用表别名限定，如o.id, u.id→ 改成o.id AS order_id, u.id AS user_id - 避免在 CTE 内部用
*，尤其跨表 JOIN 时——字段膨胀会让后续层难以维护
CTE + 窗口函数组合时，WHERE 不能直接引用窗口别名
这是高频翻车点。你写了 WITH ranked AS (SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM emp)，然后想查 rnk = 1 的记录，直觉写 SELECT * FROM ranked WHERE rnk = 1 ——看起来没问题，但部分数据库（如旧版 SQLite、某些 Hive 配置）会在 WHERE 阶段报错 no such column: rnk，因为窗口函数执行阶段晚于 WHERE。
正确做法是再套一层，或者改用 HAVING（仅限聚合上下文），但最稳妥的是把筛选条件移到外层：
WITH ranked AS ( SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM emp ) SELECT * FROM ranked WHERE rnk = 1;
注意：PostgreSQL 和 MySQL 8.0+ 允许这样写，但 Oracle 12c 之前不支持在 WHERE 中引用窗口别名，必须用子查询包裹。
- 窗口函数结果不能用于
WHERE或GROUP BY，只可用于SELECT和HAVING（后者需配合GROUP BY） - 若需按窗口结果过滤，CTE 是最干净的写法；但别指望它能减少数据量——CTE 默认不物化，
WHERE rnk = 1仍会先算全量再过滤 - 性能敏感场景，考虑用
ROW_NUMBER()替代RANK()，避免因并列排名导致意外多行
CTE 的核心价值不在“炫技”，而在把不可拆分的聚合表达式，变成可命名、可调试、可单独验证的逻辑单元。最容易被忽略的是：CTE 定义本身不执行，只有被最终 SELECT 引用时才触发计算——所以别在 CTE 里塞大表全量扫描，除非你确定后续一定会用到它。
