CREATE TABLE `t_student` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `name` varchar(128) DEFAULT NULL COMMENT '学生名称',

  `score` varchar(128) DEFAULT NULL COMMENT '分数',

  `class_id` varchar(128) DEFAULT NULL COMMENT '班级ID',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

CREATE TABLE `t_class` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `name` varchar(128) DEFAULT NULL COMMENT '班级名称',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='班级表';

select * from t_student a

where (select count(1) from t_student where class_id = a.class_id and id <> a.id and score > a.score) < 3

select b.name,b.score,c.name from t_student b

join t_class c on b.class_id=c.id

where b.id in

(select id from t_student a

where (select count(1) from t_student where class_id = a.class_id and id <> a.id and score > a.score) < 3)

group by b.class_id,b.id

order by b.class_id ,score desc;