MySQL分组查询最大值对应的记录|极客笔记

MySQL分组查询最大值对应的记录

1. 引言

在使用MySQL数据库进行数据分析和统计时，经常会遇到需要找出某个字段的最大值，并且找出该最大值对应的记录的场景。本文将重点介绍MySQL中如何使用分组查询来实现这一需求，同时给出示例代码和运行结果。

2. 数据准备

首先我们需要准备一个示例数据表students，包含以下字段：id、name和score。

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  score INT
);

INSERT INTO students (name, score)
VALUES ('Tom', 80),
       ('Alice', 90),
       ('John', 75),
       ('Sarah', 95),
       ('Bob', 85),
       ('Lily', 92);

3. 分组查询查找最大值对应的记录

我们可以使用MySQL的GROUP BY语句结合MAX()函数来实现分组查询，并找出最大值对应的记录。

SELECT s.name, s.score
FROM students s
JOIN (
  SELECT MAX(score) AS max_score
  FROM students
) m
ON s.score = m.max_score;

运行以上SQL语句，将得到以下结果：

name	score
Alice	90
Sarah	95
Lily	92

4. 运行结果解析

我们使用了一个内嵌的子查询来找出students表中score字段的最大值，将其命名为max_score，然后将该值与students表进行连接查询，找出所有分数与max_score相等的记录。

5. 拓展应用

除了查找单个字段的最大值对应的记录，我们还可以通过扩展SQL语句来满足其他需求，例如查找多个字段的最大值对应的记录、查找根据某个字段分组后的最大值对应的记录等等。

5.1 查找多个字段的最大值对应的记录

如果我们想要找到students表中，score字段最大值对应的记录，并且在存在多条记录的情况下，以id字段进行降序排序，那么可以使用以下SQL语句：

SELECT s.id, s.name, s.score
FROM students s
JOIN (
  SELECT MAX(score) AS max_score
  FROM students
) m
ON s.score = m.max_score
ORDER BY s.id DESC;

运行以上SQL语句，将得到以下结果：

id	name	score
6	Lily	92
4	Sarah	95
2	Alice	90

5.2 查找根据某个字段分组后的最大值对应的记录

如果我们想要找到students表中，根据name字段进行分组后，每个分组中score字段的最大值对应的记录，可以使用以下SQL语句：

SELECT s.name, s.score
FROM students s
JOIN (
  SELECT name, MAX(score) AS max_score
  FROM students
  GROUP BY name
) m
ON s.name = m.name AND s.score = m.max_score;

运行以上SQL语句，将得到以下结果：