在 MySQL 5.7 中可以使用变量(Variable)来模拟 Row_Number() 函数的效果,实现分组排序。
假设有如下示例数据:
id | name | score |
---|---|---|
1 | Tom | 80 |
2 | Jane | 90 |
3 | Alex | 85 |
4 | John | 75 |
5 | Lucy | 95 |
6 | Jack | 88 |
要求按照成绩(score)从高到低进行排名,并且需要在每个分数段内实现序号(row number),可以使用以下 SQL 语句实现:
SELECT
id, name, score,
@rank := IF(@prev_score = score, @rank + 1, 1) AS rank,
@prev_score := score
FROM
scores
ORDER BY
score DESC, id ASC;
解释一下上面的 SQL 语句:
- 使用 @rank 变量表示当前的序号,初始化为 1。
- 使用 @prev_score 变量记录前一个分数,初始化为 NULL。
- 在 SELECT 语句中,通过 IF 函数判断当前行的分数是否与前一行相同,如果相同,则序号加 1,否则重置为 1。
- 这里需要注意,IF 函数需要将判断分数是否相同的逻辑放在第二个参数里,将计算序号的逻辑放在第三个参数里。
- 最后需要更新 @prev_score 变量,记录当前行的分数。
执行以上 SQL 语句,会得到如下结果:
id | name | score | rank |
---|---|---|---|
5 | Lucy | 95 | 1 |
2 | Jane | 90 | 1 |
6 | Jack | 88 | 1 |
3 | Alex | 85 | 1 |
1 | Tom | 80 | 1 |
4 | John | 75 | 1 |
可见,每个分数段内的序号都正确地实现了。