SQL如何实现数据分组后取最大值的一行？窗口函数应用-SQL-PHP中文网

row_number()是最可靠方案，因max()+group by仅返回最大值而丢失整行字段；row_number()通过partition by分组、order by排序编号后筛选rn=1，可精确获取每组首行完整记录。

sql如何实现数据分组后取最大值的一行？窗口函数应用

用 `ROW_NUMBER()` 按组排序取首行最可靠

直接在 GROUP BY 后用 MAX() 只能拿到最大值本身，拿不到对应那行的其他字段（比如用户名、时间戳）。真正要“取最大值所在整行”，必须靠窗口函数。其中 ROW_NUMBER() 是最常用且语义最清晰的选择——它按分组和排序规则给每行编号，只要取编号为 1 的行即可。

常见错误是误用 RANK() 或 DENSE_RANK()：当存在并列最大值时，它们会给多行标 1，导致结果多于预期；而 ROW_NUMBER() 强制唯一编号，确保每组只返回一行（即使值相同，也会按 ORDER BY 子句里的次要字段或物理顺序区分）。

写法示例：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY category ORDER BY score DESC, id ASC
  ) AS rn
  FROM products
) t WHERE rn = 1;

PARTITION BY 决定分组依据，必须明确指定
ORDER BY 中建议补充二级排序（如 id ASC），避免因无序导致结果不稳定
注意：MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持；SQLite 3.25+ 也支持，但旧版不支持

用 `FIRST_VALUE()` 只取单个字段，不能替代整行筛选

FIRST_VALUE() 是窗口函数里容易被误解的一个：它只能返回某个字段的“第一个值”，无法返回该行其他列。如果你只需要最大分数对应的用户名，它够用；但一旦需要同时拿到分数、时间、状态等多个字段，它就无能为力了。

错误尝试：

SELECT DISTINCT category,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY score DESC) AS top_name,
  FIRST_VALUE(score) OVER (PARTITION BY category ORDER BY score DESC) AS top_score
FROM products;

—— 这看似可行，但 DISTINCT 不保证结果稳定，且无法控制同分时选哪条记录

它本质是“计算型”函数，适合做聚合旁路补充，不适合做行级过滤
性能上，FIRST_VALUE() 通常比 ROW_NUMBER() + 子查询略快（少一次嵌套），但牺牲了可控性和通用性

WHERE 子句里不能直接用窗口函数，必须嵌套或 CTE

这是初学者最常踩的坑：WHERE 执行阶段早于窗口函数计算，所以像 WHERE ROW_NUMBER() OVER (...) = 1 会报错，典型错误信息是 "window functions are not allowed in WHERE clause"。

正确做法只有两种：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t WHERE rn = 1;

或使用 CTE：

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

不能把窗口函数放在 HAVING 里替代——HAVING 是配合 GROUP BY 的聚合过滤，和窗口函数无关
某些方言（如老版本 Hive）不支持子查询中用窗口函数，此时需改用 LATERAL VIEW 或两步 MapReduce

性能敏感时注意索引与排序字段匹配

窗口函数的 ORDER BY 部分如果没走索引，会导致大量临时排序，尤其数据量大时延迟明显。比如按 score DESC 排序，但表上只有 (category) 单列索引，数据库无法跳过排序步骤。

推荐复合索引：对 PARTITION BY a ORDER BY b DESC，建索引 ON table(a, b DESC)（PostgreSQL/MySQL 8.0+ 支持降序索引）
避免在 ORDER BY 里用函数或表达式，例如 ORDER BY UPPER(name) 会让索引失效
如果只是取最大值（不要求整行），SELECT category, MAX(score) FROM t GROUP BY category 通常比窗口函数快得多，别为了统一风格硬套窗口函数

实际业务中，分组取最大值那行往往连着更新状态或生成报表，这时候 ROW_NUMBER() 的确定性比速度更重要——哪怕慢一点，也要确保每次跑出来的结果一致。