窗口函数MAX()可直接获取每行所在分组的最大值，无需先聚合再JOIN；关键要正确使用PARTITION BY（去ORDER BY得整组最大值），且因执行顺序限制，需用CTE或子查询在WHERE外过滤。
用窗口函数 MAX() 直接对比当前行
不需要先聚合再 JOIN，MAX() 作为窗口函数就能在每行上拿到同组最大值。关键在于写对 PARTITION BY 和 ORDER BY —— 如果只分组不排序，MAX() 默认是整组范围；加了 ORDER BY 就变成“到当前行为止”的累积最大值，容易误用。
常见错误是写成：MAX(value) OVER (PARTITION BY category ORDER BY id)，这实际算的是“从组开头到当前行”的最大值，不是整组最大值。要整组最大值，必须去掉 ORDER BY 或显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
- 正确写法：
MAX(value) OVER (PARTITION BY category) - 对比判断可直接写：
value = MAX(value) OVER (PARTITION BY category)，结果为TRUE/FALSE（或 1/0，取决于数据库） - PostgreSQL、SQL Server、Oracle、MySQL 8.0+ 都支持；SQLite 3.25+ 也支持，但旧版不支持窗口函数
WHERE 中不能直接用窗口函数？那就用子查询或 CTE
想筛选出“组内最大值所在的行”，不能在 WHERE 里直接写 value = MAX(value) OVER (...)，因为窗口函数不能出现在 WHERE 或 HAVING 中——执行顺序决定了它还没算出来。
解决办法是把窗口计算提到外层：要么用子查询包裹，要么用 WITH CTE 先算出最大值标记，再在外层过滤。
- CTE 示例：
WITH ranked AS ( SELECT *, value = MAX(value) OVER (PARTITION BY category) AS is_max FROM sales ) SELECT * FROM ranked WHERE is_max;
- 注意：有些数据库（如老版本 MySQL）不支持 CTE，就得用派生表（子查询）代替
- 别用
GROUP BY + HAVING模拟——会丢失非分组列，除非配合ANY_VALUE()或额外 JOIN，反而更绕
性能敏感时，避免重复计算 MAX()
如果 SQL 里多次引用同一个 MAX(value) OVER (...)，比如同时用于比较、排序、生成新列，不同数据库优化能力不同：PostgreSQL 通常能复用计算结果，MySQL 8.0 可能重复执行窗口函数，尤其数据量大时明显变慢。
- 推荐统一用 CTE 提前计算，后续多处引用同一个别名
- 别写成：
SELECT ..., value = MAX(...) OVER (...), other_col > MAX(...) OVER (...)—— 这里MAX()被算了两次 - 小数据量无所谓，但千万级表 + 多个窗口函数时，CTE 的可读性和性能优势都很实在
DENSE_RANK() 和 RANK() 能替代吗？
可以，但语义不同：用 RANK() OVER (PARTITION BY category ORDER BY value DESC) 算排名，再判断 rank = 1，也能找出最大值行。但它处理并列时行为不一样——RANK() 并列后会跳号（比如两个第一，下一个是第三），DENSE_RANK() 不跳号（两个第一，下一个是第二）。
- 如果只要“最大值”，且允许多个相同最大值都选中，
MAX()更直观、无歧义 - 如果业务逻辑明确要求“排名第一的行”，且需区分并列场景，才考虑
RANK()或DENSE_RANK() - 性能上，
MAX()窗口函数通常比排序类窗口函数开销略低，毕竟不用维护完整排序状态
真正容易被忽略的是：窗口函数的执行时机和作用域。很多人以为 MAX() OVER (PARTITION BY x) 是“先分组再求最大”，其实它是按整个结果集扫描一遍完成的，不依赖 GROUP BY 子句是否存在——这意味着即使你没写 GROUP BY，只要用了窗口函数，它就在那个逻辑分区里生效。
