mysql 分组查询后,获取第一条和最后一条|极客笔记

mysql 分组查询后,获取第一条和最后一条

在实际的数据库查询中，经常会遇到需要对数据进行分组并且要获取每组中的第一条和最后一条记录的需求。在MySQL中，可以通过使用子查询和LIMIT语句来实现这一功能。本文将详细介绍如何在分组查询后获取每组的第一条和最后一条记录。

1. 使用子查询获取每组的第一条记录

首先，我们需要进行一个分组查询，然后通过子查询来获取每组的第一条记录。假设我们有一个名为students的表，其中包含学生的姓名和分数信息。现在我们需要按照分数进行分组，并获取每组中分数最高的学生的信息。

SELECT s1.*
FROM students s1
JOIN (
    SELECT MAX(score) AS max_score
    FROM students
    GROUP BY score
) s2
ON s1.score = s2.max_score;

上面的SQL语句先对students表进行分组查询，找出每组中的最高分数，然后通过子查询将最高分数与原表连接，找到对应最高分数的学生信息。这样就实现了获取每组的第一条记录的功能。

2. 使用子查询获取每组的最后一条记录

类似地，我们可以通过子查询来获取每组的最后一条记录。假设我们需要获取每组中最后一名学生的信息，可以按照以下方法来实现。在students表中，假设有id字段表示学生编号。

SELECT s1.*
FROM students s1
JOIN (
    SELECT id, MAX(id) AS max_id
    FROM students
    GROUP BY score
) s2
ON s1.id = s2.max_id;

这里的SQL语句与上一节类似，只不过这次我们要根据学生的编号id来获取每组最后一名学生的信息。通过子查询获取每组的最后一条记录，完成了我们的目标。

3. 完整示例

为了更好地理解上述操作，我们来看一个完整的示例。假设我们有以下students表：

id	name	score
1	Alice	80
2	Bob	90
3	Cathy	80
4	David	85
5	Eva	95

现在我们需要按照分数分组，并获取每组中分数最高的学生和最低的学生的信息。我们可以按照以下方法来实现：

-- 获取每组中分数最高的学生的信息
SELECT s1.*
FROM students s1
JOIN (
    SELECT MAX(score) AS max_score
    FROM students
    GROUP BY score
) s2
ON s1.score = s2.max_score;

-- 获取每组中分数最低的学生的信息
SELECT s1.*
FROM students s1
JOIN (
    SELECT MIN(score) AS min_score
    FROM students
    GROUP BY score
) s2
ON s1.score = s2.min_score;

运行以上SQL语句，我们可以得到分数最高的学生和分数最低的学生。在这个示例中，分数最高的学生为Eva，分数最低的学生为Alice。