---
url: /article/6aoedo6i/index.md
---
## 前言

在开发中分组查询是很常见的，而对组内排序该如何做？组内排序后再取前 N 条又怎么做？
乍一看似乎不难，但一时间也不一定想得出来，故思考过后记录于此。

## 1. 前置准备

### 1.1 创建测试表

```sql
CREATE TABLE `student_scores` (
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `stu_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
  `stu_score` float NOT NULL COMMENT '学生分数',
  `stu_class` varchar(10) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生分数';
```

### 1.2 插入测试数据

插入准备好的数据：

```sql
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b410-0a0b-11ee-a149-0242ac110002', 'bobby1', 88.5, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b8ab-0a0b-11ee-a149-0242ac110002', 'bobby2', 98.3, 'class2');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b934-0a0b-11ee-a149-0242ac110002', 'regan1', 76.1, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b970-0a0b-11ee-a149-0242ac110002', 'regan2', 63.2, 'class2');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b99d-0a0b-11ee-a149-0242ac110002', 'pony1', 65.4, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b9cc-0a0b-11ee-a149-0242ac110002', 'pony2', 86.2, 'class2');
```

或使用插入语句：

```sql
INSERT INTO student_scores(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES
(UUID(),'bobby1',88.5,'class1'),
(UUID(),'bobby2',98.3,'class2'),
(UUID(),'regan1',76.1,'class1'),
(UUID(),'regan2',63.2,'class2'),
(UUID(),'pony1',65.4,'class1'),
(UUID(),'pony2',86.2,'class2');
```

## 2. 实现方式

### 2.1 Mysql 8

Mysql 8 新增了几个开窗函数，我们使用其中的`ROW_NUMBER()`，它将为分区中的每一行分配一个顺序整数。因此，我们可以很方便的实现上述需求。

```sql
SELECT id,stu_name,stu_score,stu_class FROM (
SELECT id,stu_name,stu_score,stu_class,ROW_NUMBER() OVER(PARTITION BY stu_class ORDER BY stu_score desc) num FROM student_scores
) t WHERE t.num <= 2;
```

Sql 解析：

1. `PARTITION BY stu_class ORDER BY stu_score desc`将表中数据按`stu_class`分区，并按`stu_score`倒序，`ROW_NUMBER()`将为组内每行数据生成一个序号，从 1 开始依次递增；
2. 然后再查询表中所有的字段，用 num 条件筛选；

结果如下：

|id|stu\_name|stu\_score|stu\_class|
|--|--|--|--|
|69c4b410-0a0b-11ee-a149-0242ac110002|bobby1|88.5|class1|
|69c4b934-0a0b-11ee-a149-0242ac110002|regan1|76.1|class1|
|69c4b8ab-0a0b-11ee-a149-0242ac110002|bobby2|98.3|class2|
|69c4b9cc-0a0b-11ee-a149-0242ac110002|pony2|86.2|class2|

### 2.2 Mysql 5.7

一些老项目可能使用的是 5.7 版本，不方便升级，而这个版本没有开窗函数，实现起来相对麻烦一点。

```sql
SELECT id,stu_name,stu_score,stu_class FROM (
SELECT a.id,
stu_name,
stu_score,
@num := (IF(@class  = a.stu_class,@num +1,1)) as num,
@class := a.stu_class as stu_class
FROM 
(SELECT stu_class,id,stu_name,stu_score FROM student_scores ORDER BY stu_class asc,stu_score desc) a,
(SELECT @num:=0,@class :='' COLLATE utf8mb4_general_ci) b) t WHERE t.num <= 2;
```

Sql 解析：

1. 对表`student_scores`的`stu_class`升序，以实现类似分组的效果，同时对`stu_score`降序，以实现分组排序的效果。这里我们可以发现，要实现分组效果不一定非要用`GROUP BY`；
2. 定义两个变量`@num`默认赋值`0`，`@class`默认赋值`''`。这里要注意为变量`@class`使用`COLLATE`关键字指定排序规则，必须要和表的排序规则一致，才能进行字段比较；
3. 从第一行数据开始，比较`@class  = a.stu_class`是否相等，由于`@class`初值为`''`，所以必定不相等，则`@num`被赋值为1，然后`@class`的值被赋值为第一行的`stu_class`的值。接着第二行的时候，`@class`的变量值与`stu_class`的值是相等的，因此`@num:=@num+1`，实现了序号递增。等到`@class`的值发生变化时，即开始了一个新的分组，此时`@num`将被赋值为1，然后在组内递增；
4. 千万注意，`@class := a.stu_class`一定要在`@num := (IF(@class  = a.stu_class,@num +1,1))`之后，此逻辑才能运行正确的结果；

结果如下：

|id|stu\_name|stu\_score|stu\_class|
|--|--|--|--|
|69c4b410-0a0b-11ee-a149-0242ac110002|bobby1|88.5|class1|
|69c4b934-0a0b-11ee-a149-0242ac110002|regan1|76.1|class1|
|69c4b8ab-0a0b-11ee-a149-0242ac110002|bobby2|98.3|class2|
|69c4b9cc-0a0b-11ee-a149-0242ac110002|pony2|86.2|class2|

## 思考

当需要进行分组查询时，不能思维定式的就想到用`GROUP BY`来解决，在`ONLY_FULL_GROUP_BY`模式下反而会更麻烦。而使用多字段排序更容易实现分组排序的效果。

***

::: note 本文参考

* [MySQL8.0开窗函数](https://blog.csdn.net/kejiayuan0806/article/details/103297893)

:::