MySQL 分组求 Top N 问题

数据表及字段信息：

1
2
3
4
5

CREATE TABLE `lesson` (
  `sno` varchar(100) DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

第一列no为学号，第二列course为课程，第三列score为分数

Hive中使用函数求：

1
2
3
4
5
6
7

select a.sno,a.course,a.score
from
(
select course,score,row_number() over(partition by course order by score desc) as rn
from lesson
)a
where a.rn<=2;

row_number() over(partition by course order by score desc)为hive中的函数
意思是以课程分组，按成绩递减排序，并为每组中的数据打上行号的标记，从1开始。

在MySQL中查询：

1
2
3
4
5
6
7
8
9

select sno,course,score
from lesson a
where 3 >
(
select count(1)
from lesson b
where a.score<b.score and a.course=b.course
)
order by a.course,a.score desc ;

这种只适合没有重复值的情况，当score有重复值的时候，查询结果是不正确的

优化：使用动态sql进行查询

1
2
3
4
5
6
7
8
9

SET @row=0;
SET @groupid='';
select a.course,a.score
from
(
select sno,course,score,case when @groupid=course then @row:=@row+1 else @row:=1 end rownum,@groupid:=course from lesson
order by course,score desc
)a
where a.rownum<=5;

@row用于统计行号，@groupid用于分组，记录该组的名称

按照分组名course和需要的排序score递增进行排序，这样相同课程就会排在一起，且相同的课程之间按照成绩排序。

取出一条数据，如果该条数据的course与@group相同，则意味着是相同课程之间的比较，那么@row自加1，否则意味着该条数据是另一门课程的第一条数据，则@row=1。

这样每个课程就能够按照成绩排序并标记上行号，那么外层只需要rownum<=2即可得到每门课的前2个最高分

• ← Previous Post
• Next Post →