创建表 CREATE TABLE `score` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8; 添加数据 insert into test1(name,course,score) values ('张三','语文',80), ('李四','语文',90), ('王五','语文',93), ('张三','数学',77), ('李四','数学',68), ('王五','数学',99), ('张三','英语',90), ('李四','英语',50), ('王五','英语',89) 查询每门课前两名 select t.id`name`,course,score,ranks FROM ( SELECT @row_num:=if( @course=course, @row_num + 1,1) as ranks, @course:=course as course2, a.* FROM test1 a,( SELECT @row_num := 0 ) aa ORDER BY a.course,a.score DESC ) as t HAVING t.ranks < 3