MySQL分组且每个组取10条|极客笔记

MySQL分组且每个组取10条

在使用MySQL数据库进行数据查询时，经常会遇到需要对数据进行分组，但同时又需要在每个分组中取出一定数量的数据的情况。本文将详细介绍如何使用MySQL来实现分组并在每个组中取出指定数量的数据。

准备工作

在开始之前，我们需要先创建一个示例表格来演示我们的查询操作。假设我们有一个名为students的表，其中包含了学生的姓名（name）、年龄（age）和成绩（score）等字段。我们可以使用以下SQL语句来创建这个表格：

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

INSERT INTO students (name, age, score) VALUES
('Alice', 18, 90),
('Bob', 20, 85),
('Charlie', 19, 88),
('David', 18, 92),
('Emma', 20, 87),
('Frank', 19, 89),
('Grace', 18, 91),
('Henry', 20, 86),
('Ivy', 19, 90),
('Jack', 18, 94),
('Kate', 20, 88),
('Leo', 19, 93);

分组并取10条数据

现在我们将通过一个示例来演示如何对学生按照年龄进行分组，并在每个年龄组中取出成绩最高的前两名学生。我们可以使用以下SQL语句来实现这个目标：

SELECT id, name, age, score
FROM (
    SELECT id, name, age, score,
           ROW_NUMBER() OVER (PARTITION BY age ORDER BY score DESC) AS row_num
    FROM students
) AS ranked
WHERE row_num <= 2
ORDER BY age, score DESC;

在这个查询语句中，我们首先使用子查询SELECT id, name, age, score, ROW_NUMBER() OVER (PARTITION BY age ORDER BY score DESC) AS row_num FROM students来为每个年龄组内的学生按照成绩进行排名，其中PARTITION BY age表示按照年龄进行分组，ORDER BY score DESC表示按照成绩降序排序，ROW_NUMBER()函数则会为每个学生分配一个排名。然后在主查询中，我们筛选出排名在前两名的学生并按照年龄和成绩降序排序。

运行结果

根据上面的查询语句，我们可以得到如下结果：

+----+-------+-----+-------+
| id | name  | age | score |
+----+-------+-----+-------+
|  8 | Henry |  20 |    86 |
| 11 | Kate  |  20 |    88 |
|  2 | Bob   |  20 |    85 |
|  3 | Charlie | 19 |    88 |
|  6 | Frank | 19 |    89 |
| 12 | Leo   | 19 |    93 |
|  4 | David | 18 |    92 |
|  7 | Grace | 18 |    91 |
| 10 | Jack  | 18 |    94 |
+----+-------+-----+-------+

从结果中我们可以看到，每个年龄组中分别取出了两名成绩最高的学生，按照成绩降序排列。这样我们就成功地实现了在分组的情况下取出每个组内指定数量的数据。