SQL嵌套查询如何与窗口函数配合使用？

<p>窗口函数不能直接出现在WHERE、GROUP BY或HAVING中；必须先在子查询或CTE中计算，再在外层引用，否则如SELECT * FROM t WHERE ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)会报错。</p>

窗口函数不能直接出现在 WHERE、GROUP BY 或 HAVING 中，这是绝大多数人写错的第一步。必须把窗口计算结果放到子查询或 CTE 里，再在外层引用。

WHERE 里直接写 ROW_NUMBER() 就会报错

典型错误是：SELECT * FROM t WHERE ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) —— 这会触发 <code>Unknown column 或 Window function is not allowed in this context。因为 SQL 执行顺序中，WHERE 在 SELECT（含窗口函数）之前运行。

• 正确做法：用子查询或 CTE 先算出编号，外层再 WHERE rn
• 别漏掉 PARTITION BY 和显式 ORDER BY，否则编号无意义
• 如果排序字段有重复值（比如多个订单同时间），加一个唯一字段如 id 保序：ORDER BY created_at DESC, id DESC

GROUP BY 外层不能直接依赖 LAG/LEAD 结果

想按“与前一日差值是否为正”分组？GROUP BY LAG(amount) OVER (ORDER BY date) 是非法的。窗口函数输出不可用于分组键，除非先物化。

• 必须先用 CTE 或子查询生成 prev_amount 字段，再在外层 GROUP BY 引用它
• MySQL 8.0+ 支持在派生表中用 LAG，但外层若再加 GROUP BY，仍需确保排序上下文不丢失
• PostgreSQL 更宽松，但如果子查询用了 ORDER BY 而外层没重排，LAG 可能错位

嵌套子查询里 ORDER BY 不等于窗口函数的排序依据

子查询里写 ORDER BY date 只影响结果集顺序，对内层 ROW_NUMBER() OVER (ORDER BY ...) 没作用。窗口函数的排序必须显式写在 OVER 子句里。

• ROW_NUMBER() OVER (ORDER BY date) 和子查询的 ORDER BY date 是两回事，不能互相替代
• 如果窗口函数里漏了 ORDER BY，编号结果不可预测，尤其在 MySQL 中可能每次执行都不同
• 日期字段是 VARCHAR 类型时，隐式转换可能导致排序错乱，务必确认类型一致

CTE 比嵌套子查询更可靠，尤其涉及多层窗口

当需要同时用 ROW_NUMBER() 和 LAG()，再做聚合时，CTE 能清晰隔离每一步逻辑，避免优化器多次扫描。

• MySQL 8.0 的 CTE 默认可物化，比老式 FROM (SELECT ...) t 更稳
• 不要在一个 SELECT 中堆多个不同 PARTITION BY + 不同 ORDER BY 的窗口函数——PostgreSQL 和 SQL Server 很可能分别排序，性能翻倍
• 如果只是要总行数这类全局指标，COUNT(*) OVER () 看似方便，但空 OVER() 在 PostgreSQL 中会触发全表排序，不如先查 COUNT(*) 再 JOIN

真正容易被忽略的是：窗口函数的执行时机晚于 WHERE 但早于外层 ORDER BY，所以它的输入数据已经过过滤，但输出列不能反向影响过滤条件——所有依赖窗口结果的逻辑，必须放在第二层查询里。