SQL中如何比较当前行与组内最大值？-SQL-PHP中文网

窗口函数max()可直接获取每行所在分组的最大值，无需先聚合再join；关键要正确使用partition by（去order by得整组最大值），且因执行顺序限制，需用cte或子查询在where外过滤。

sql中如何比较当前行与组内最大值？

用窗口函数 `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，只要用了窗口函数，它就在那个逻辑分区里生效。