mysql top查询效率_MySQL 查询组内 TOP N

测试数据

id

username

subject

score

1

张三

语言

78

2

张三

数学

96

3

张三

外语

73

4

张三

历史

87

5

李四

语言

90

6

李四

数学

22

7

李四

外语

80

8

李四

历史

89

9

王五

语言

83

10

王五

数学

85

11

王五

外语

79

12

王五

历史

68

13

赵六

语言

88

14

赵六

数学

90

15

赵六

外语

93

16

赵六

历史

79

查询需求

查询出各科成绩的前2名

解决方案

MySQL 8 以前的版本

方法一：使用会话变量

这种方法的思路如下：

1. 组内排序

2. 组内排序后，按顺序给组内每条记录添加 `rank` 值， `rank` 值是从1开始递增的

3. 查询 `rank <= N` 的记录

set @current_subject = null;

set @current_score = null;

select id, username, subject, score

from (

select id,

username,

subject,

score,

@score_rank := IF(@current_subject = subject, IF(@current_score = score, @score_rank, @score_rank + 1),

1) AS score_rank,

@current_subject := subject,

@current_score := score

from test_score

order by subject, score desc) tmp_table

where score_rank <= 2;

核心语句

@score_rank := IF(@current_subject = subject, IF(@current_score = score, @score_rank, @score_rank + 1), 1) AS score_rank

首先组内排序后的结果如下：

id

username

subject

score

8

李四

历史

89

4

张三

历史

87

16

赵六

历史

87

12

王五

历史

68

15

赵六

外语

93

7

李四

外语

80

11

王五

外语

79

3

张三

外语

73

2

张三

数学

96

14

赵六

数学

90

10

王五

数学

85

6

李四

数学

22

5

李四

语言

90

13

赵六

语言

88

9

王五

语言

83

1

张三

语言

78

以 历史科目 组的成绩为例，展示 score_rank 的计算过程

id

username

subject

score

备注

8

李四

历史

89

@current_subject 初始为 null, 与该行的 subject 不相同，所以 @score_rank 被赋值为 1

4

张三

历史

87

@current_subject 此时已被赋值为 历史, 与该行的 subject 相同，但在处理上一条数据时 @current_score 已被赋值为 89，与该行的 score 值不相等，所以 @score_rank 被赋值为 @score_rank + 1 即值 2

16

赵六

历史

87

@current_subject 此时已被赋值为 历史, 与该行的 subject 相同，但在处理上一条数据时 @current_score 已被赋值为 87，与该行的 score 值相等，所以 @score_rank 被赋值为 @score_rank 即值 2

12

王五

历史

68

@current_subject 此时已被赋值为 历史, 与该行的 subject 相同，但在处理上一条数据时 @current_score 已被赋值为 89，与该行的 score 值不相等，所以 @score_rank 被赋值为 @score_rank + 1 即值 3

其他

set @current_subject = null;

set @current_score = null;

上面的两行不是必须的，之所以加上，是为了避免在同一个 session 中已经使用了相同的变量并为其赋了值，从而可能导致查询结果不正确的情况。

方法二：自连接

思路：

1. 组内排序

2. 取出组内的一条数据

1. 如果同组内没有比当前分数大的数据，则当前数据就是最大的数据

2. 如果同组内有 1 条数据的分数比当前分数大，则当前数据是第 2 大

3. 如果同组内有 2 条数据的分数比当前分数大，则当前数据是第 3 大

4. …………

3. 找出同组内少于 2 条数据比当前数据分数大的数据

select t1.*

from test_score t1

left join test_score t2 on t1.subject = t2.subject and t1.score < t2.score

group by t1.username, t1.subject, t1.score

having count(t2.id) < 2

order by t1.subject, t1.score desc;

count(t2.id) 就是有几条数据的分数比当前数据大。

这种方法有个缺点，就是不能正确处理分数相同的数据。

MySQL 8

MySQL 8 已经支持 row_number、rank、dense_rank、over函数。

使用 rank() 函数

select id, username, subject, score

from (select id, username, subject, score, rank() over (partition by subject order by score desc) rank_

from test_score) tmp

where tmp.rank_ <= 2;

完美！

使用 row_number() 函数

select id, username, subject, score

from (select id, username, subject, score, row_number() over (partition by subject order by score desc) row_number_

from test_score) tmp

where tmp.row_number_ <= 2;

这种方法也有不能正确处理分数相同的数据的缺点。