遇到一个场景，要把数据分组，然后获取每组前10条数据，首先我想到用group by分组，但是难点是分组后怎么知道该数据在组里面排第几条。
一、创建表，插入相关测试数据create table `score` ( `id` int(11) not null auto_increment comment '主键', `subject` varchar(20) default null comment '科目', `student_id` int(11) default null comment '学生id', `student_name` varchar(20) not null comment '学生姓名', `score` double default null comment '成绩', primary key (`id`)) engine=innodb auto_increment=16 default charset=utf8;
备注：插入的数据sql在最后面，小伙伴可以自行验证下面的sql
二、查询每科成绩前三的记录数据有了，那么写sql,sql如下：
###每科成绩前三名select * from score s1 where ( select count( * ) from score s2 where s1.`subject` = s2.`subject` and s1.score < s2.score ) < 3 order by subject, score desc
分析：
里面用了子查询，核心sql是where后面的这个条件：
( select count( * ) from score s2 where s1.subject = s2.subject and s1.score < s2.score ) < 3
这段sql的意思是。。。
感觉我的语言有点描述不出来，还是用我熟悉的java代码描述上面的sql,大概就是for循环遍历两次，在第二次for循环的时候统计同一科目的学生记录，比s1的学生分数高的数量，如果这个数量小于3的话，说明s1排名前三，看下面的代码理解理解
public class studenttest { public static void main(string[] args) { list<student> list = new arraylist<>(); //初始化和表结构一致的数据 initdata(list); //记录查询出来的结果 list<student> result = new arraylist<>(); for(student s1 : list){ int num = 0; //两次for循环遍历，相当于sql里面的子查询 for(student s2:list){ //统计同一科目，且分数s2分数大于s1的数量，简单理解就是同一科目的学生记录，比s1的学生分数高的数量 if(s1.getsubject().equals(s2.getsubject()) &&s1.getscore()<s2.getscore()){ num++; } } //比s1的学生分数高的数量,如果小于3的话，说明s1这个排名前三 // 举例：num=0时，说明同一科目，没有一个学生成绩高于s1学生， s1学生的这科成绩排名第一 // num =1,时，s1学生排名第二，num=3时：说明排名同一科目有三个学生成绩高过s1，s1排第四，所以只统计前三的学生，条件就是num<3 if(num < 3){ result.add(s1); } } //输出各科成绩前三的记录 result.stream() .sorted(comparator.comparing(student::getsubject)) .foreach( s-> system.out.println(string.format("学生:%s,科目：%s,成绩:%s",s.getname(),s.getsubject(),s.getscore())) ); } public static void initdata(list<student> list) { list.add(new student(1,"语文","张三",59)); list.add(new student(2,"数学","张三",78)); list.add(new student(3,"英语","张三",65)); list.add(new student(4,"语文","李四",88)); list.add(new student(5,"数学","李四",58)); list.add(new student(6,"英语","李四",65)); list.add(new student(7,"语文","王五",92)); list.add(new student(8,"数学","王五",99)); list.add(new student(9,"英语","王五",96)); list.add(new student(10,"语文","小张",90)); list.add(new student(11,"数学","小张",91)); list.add(new student(12,"英语","小张",90)); list.add(new student(13,"语文","小华",88)); list.add(new student(14,"数学","小华",79)); list.add(new student(15,"英语","小华",77)); } @data public static class student { private int id; private string subject; private string name; private double score; //想当于表结构 public student(int id, string subject, string name, double score) { this.id = id; this.subject = subject; this.name = name; this.score = score; }}
可以看到代码运行完打印出来的结果和执行sql后的结果是一样的
三、查询学生各科分数大于等于90分的记录表和数据都有了，顺便也总结一些这类型的sql题
如题目为查询上面表的各科成绩都大于等于90分的记录，那么sql怎么写？
1. 第一种写法：正向思考各科成绩都大于90分的，那么最低分的也必须大于等于90分，sql如下：
select * from score where student_id in (select student_id from score group by student_id having min( score ) >= 90 )
2. 第二种写法：逆向思考排除最高分都小于90分的记录
select * from score where student_id not in (select student_id from score group by student_id having max( score ) < 90 )
备注：正向和逆向看具体情况选择
其他的插叙
查询学生各科平均分大于80分的记录
###查询学生各科平均分大于80分的记录select * from score where student_id in( select student_id from score group by student_id having avg(score)>80)
查询一个学生每科分数不及格的记录
###查询一个学生每科分数不及格的记录select * from score where student_id in ( select student_id from score group by student_id having max( score ) < 60 )
附：表结构插入的sql
create table `score` ( `id` int(11) not null auto_increment comment '主键', `subject` varchar(20) default null comment '科目', `student_id` int(11) default null comment '学生id', `student_name` varchar(20) not null comment '学生姓名', `score` double default null comment '成绩', primary key (`id`)) engine=innodb auto_increment=16 default charset=utf8;insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (1, '语文', 1, '张三', 59);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (2, '数学', 1, '张三', 78);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (3, '英语', 1, '张三', 65);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (4, '语文', 2, '李四', 88);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (5, '数学', 2, '李四', 58);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (6, '英语', 2, '李四', 65);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (7, '语文', 3, '王五', 92);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (8, '数学', 3, '王五', 99);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (9, '英语', 3, '王五', 96);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (10, '语文', 4, '小张', 90);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (11, '数学', 4, '小张', 91);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (12, '英语', 4, '小张', 90);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (13, '语文', 5, '小华', 88);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (14, '数学', 5, '小华', 79);insert into `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) values (15, '英语', 5, '小华', 77);
以上就是mysql实现按组区分后获取每组前几名的sql怎么写的详细内容。