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 ;

结果: