MySQL实现分组排序

场景描述：如下表，需从每个分组中找到分数排名前二的用户。先来做个简单的分组排序：SELECT * FROM `test` ORDER BY group_name, score DESC得到的结果如上图，显然，还需移除掉用户一和用户四。给每条数据分配序号（类似于Oracle中的row_number()，Mysql中没有此函数，故手动实现）SELECTt.*,IF( t.group_name = @G

羊毛韭菜

5179人浏览 · 2022-01-11 14:38:11

羊毛韭菜 · 2022-01-11 14:38:11 发布

场景描述：如下表，需从每个分组中找到分数排名前二的用户。
在这里插入图片描述

一、Mysql 8.0+ 版本已经支持row_number()函数，可以直接使用：

SELECT
	* 
FROM
	( SELECT *, row_number() over ( PARTITION BY group_name ORDER BY score DESC ) AS rn FROM `test` ) t 
WHERE
	t.rn <= 2;

二、下面重点说一下低于 8.0 版本的实现：

先来做个简单的分组排序：
```
SELECT * FROM `test` ORDER BY group_name, score DESC
```
得到的结果如上图，显然，还需移除掉用户一和用户四。

给每条数据分配序号（类似于row_number()）

SELECT
	t.*,
IF
	( t.group_name = @GROUP_NAME, @ROWNUM := @ROWNUM + 1, @ROWNUM := 1 ) ROWNUM,
	@GROUP_NAME := t.group_name group_name2 
FROM
	`test` t,
	( SELECT @ROWNUM := 0, @GROUP_NAME := NULL ) r -- 定义两个参数@ROWNUM、@GROUP_NAME，并赋值
ORDER BY
	t.group_name,
	t.score DESC

在这里插入图片描述
结果如上图（主要看ROWNUM字段）

接下来就是简单的取数了

SELECT
		* 
	FROM
		(
		SELECT
			t.*,
		IF
			( t.group_name = @GROUP_NAME, @ROWNUM := @ROWNUM + 1, @ROWNUM := 1 ) ROWNUM,
			@GROUP_NAME := t.group_name group_name2 
		FROM
			`test` t,
			( SELECT @ROWNUM := 0, @GROUP_NAME := NULL ) r 
		ORDER BY
			t.group_name,
			t.score DESC 
		) temp 
	WHERE
		temp.ROWNUM <= 2