mysql实现按组区分后获取每组前几名的sql怎么编辑
|
本篇内容介绍了“mysql实现按组区分后获取每组前几名的sql怎么写”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 遇到一个场景，要把数据分组，然后获取每组前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 DES 分析： 里面用了子查询，核心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怎么写”的内容就介绍到这里了，感谢大家的阅读。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
