Mysql group by top n问题

创建表

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