MySQL 分组排序问题
SQL好久不写了,有些生疏了,一个分组排序问题想了快半天,整理下。
学生表
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='班级表';
问题:查询出每个班级前三名的信息?
首先,如何查询每个班级前三名的SQL呢?
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
完整SQL如下
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;