mysql实现row_number() over功能

想要获取每组的前n个记录，由于mysql中没有row_number() over 函数，所以自己需要通过使用mysql的语法同样实现该功能。

#建表语句

SET FOREIGN_KEY_CHECKS=0;

-- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `s_id` int(11) DEFAULT NULL, `c_id` int(11) DEFAULT NULL, `s_core` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 1, 80); INSERT INTO `score` VALUES (1, 2, 90); INSERT INTO `score` VALUES (1, 3, 99); INSERT INTO `score` VALUES (2, 1, 70); INSERT INTO `score` VALUES (2, 2, 60); INSERT INTO `score` VALUES (2, 3, 80); INSERT INTO `score` VALUES (3, 1, 80); INSERT INTO `score` VALUES (3, 2, 80); INSERT INTO `score` VALUES (3, 3, 80); INSERT INTO `score` VALUES (4, 1, 50); INSERT INTO `score` VALUES (4, 2, 30); INSERT INTO `score` VALUES (4, 3, 20); INSERT INTO `score` VALUES (5, 1, 76); 3INSERT INTO `score` VALUES (5, 2, 87); #student表

SET FOREIGN_KEY_CHECKS=0;

-- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL, `sname` varchar(255) DEFAULT NULL, `sex` varchar(255) DEFAULT NULL, PRIMARY KEY (`sid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, 赵磊, 男); INSERT INTO `student` VALUES (2, 千点, 男); INSERT INTO `student` VALUES (3, 孙峰, 男); INSERT INTO `student` VALUES (4, 李云, 女); INSERT INTO `student` VALUES (5, 周妹, 女);

#要求是：按课程类目分组，求出每组的前三位

set @rank:=0; select a.*,b.c_id,b.s_core,b.rank from( select *,@rank:=case when @current_id<>c_id then 1 else @rank+1 end as rank, @current_id:=c_id from score order by c_id,s_core desc )b left join student a on a.sid=b.s_id having rank<=3 order by b.c_id,rank ;

结果: