测试数据
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;
这种方法也有不能正确处理分数相同的数据的缺点。