场景描述:如下表,需从每个分组中找到分数排名前二的用户。
一、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 版本的实现:
1. 先来做个简单的分组排序:
SELECT * FROM `test` ORDER BY group_name, score DESC
得到的结果如上图,显然,还需移除掉用户一和用户四。
2. 给每条数据分配序号(类似于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字段)
3. 接下来就是简单的取数了
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