SQL JOIN后如何获取每组最大值对应记录-SQL-PHP中文网

直接用 GROUP BY + MAX() 拿不到完整记录，因为 MAX() 只返回字段值，不保留整行上下文，name 等非聚合字段可能与最高分记录不匹配；推荐用 ROW_NUMBER() 窗口函数按组排序取首行，或用相关子查询、自连接等兼容方案。

sql join后如何获取每组最大值对应记录

为什么直接用 GROUP BY + MAX() 拿不到完整记录

因为 MAX() 是聚合函数，它只返回字段值，不保留整行上下文。比如你写 SELECT user_id, MAX(score), name FROM scores GROUP BY user_id，name 值可能来自任意一条记录，和 MAX(score) 对应的那条记录完全无关——这是 MySQL 5.7+ 严格模式下甚至会报错 Expression #3 of SELECT list is not in GROUP BY clause 的根本原因。

用窗口函数 ROW_NUMBER() 最稳妥（推荐 PostgreSQL / SQL Server / MySQL 8.0+）

核心思路：先按分组排序，再取每组序号为 1 的行。比子查询或自连接更直观、性能通常更好。

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC) 给每个 user_id 组内按 score 降序编号
外层筛选 rn = 1 即可拿到每组最高分对应完整记录
注意：如果存在并列最高分，ROW_NUMBER() 只取其中一个（不确定是哪条）；要保留全部并列记录，改用 RANK() 或 DENSE_RANK()

SELECT user_id, score, subject, created_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC, created_at DESC) AS rn
  FROM scores
) t
WHERE rn = 1;

兼容老版本 MySQL（5.7 或更低）用相关子查询

当窗口函数不可用时，这是最通用的写法，但要注意性能陷阱。

子查询必须关联外层表（如 s2.user_id = s1.user_id），否则变成笛卡尔积
子查询里用 MAX(score) 找出该用户的最高分，外层再匹配这个分数
如果同一用户有多个相同最高分记录，会全部返回——这点反而比 ROW_NUMBER() 更符合某些业务需求
没有索引时，对大表可能很慢；建议在 (user_id, score) 上建联合索引

SELECT s1.*
FROM scores s1
WHERE s1.score = (
  SELECT MAX(s2.score)
  FROM scores s2
  WHERE s2.user_id = s1.user_id
);

JOIN 自连接容易漏掉 NULL 或重复数据

用 LEFT JOIN 找“不存在更高分”的记录，逻辑正确但细节极易出错。

必须用 LEFT JOIN，且 ON 条件包含分组键和“更大”判断：s1.user_id = s2.user_id AND s1.score
最后用 WHERE s2.user_id IS NULL 筛出没被“打败”的记录
如果某用户所有 score 都是 NULL，这条记录会被过滤掉——NULL 不参与 比较，导致 s2 总是匹配不上，s2.user_id IS NULL 成立，但结果可能不符合预期
若存在完全相同的多条最高分记录，会因笛卡尔积重复输出