mysql实现按组区分后获取每组前几名的sql怎么写

本篇内容介绍了“MySQL实现按组区分后获取每组前几名的sql怎么写”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

创新互联基于成都重庆香港及美国等地区分布式IDC机房数据中心构建的电信大带宽,联通大带宽,移动大带宽,多线BGP大带宽租用,是为众多客户提供专业成都电信服务器托管报价,主机托管价格性价比高,为金融证券行业服务器托管,ai人工智能服务器托管提供bgp线路100M独享,G口带宽及机柜租用的专业成都idc公司。

    遇到一个场景,要把数据分组,然后获取每组前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;

    mysql实现按组区分后获取每组前几名的sql怎么写

    备注:插入的数据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

    mysql实现按组区分后获取每组前几名的sql怎么写

    分析:

    里面用了子查询,核心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后的结果是一样的

    mysql实现按组区分后获取每组前几名的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怎么写”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站,小编将为大家输出更多高质量的实用文章!

    本文题目:mysql实现按组区分后获取每组前几名的sql怎么写
    浏览路径:https://www.cdcxhl.com/article30/pjidpo.html

    成都网站建设公司_创新互联,为您提供网页设计公司网站排名网站设计品牌网站设计微信小程序网站制作

    广告

    声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联

    成都app开发公司