首页 > 资讯 > 数据库 >mysql实现按组区分后获取每组前几名的sql写法

634

分享到

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

mysql按组区分 mysql获取每组前几名 mysql区分 2023-03-20 09:03:36 634人浏览独家记忆

摘要

目录一、创建表，插入相关测试数据二、查询每科成绩前三的记录三、查询学生各科分数大于等于90分的记录1. 第一种写法：正向思考2. 第二种写法：逆向思考总结遇到一个场景，要把数据分组，然后获取每组前10条数据，首先我想到用

一、创建表，插入相关测试数据

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);

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

您可能感兴趣的文档:

--结束END--

本文标题: mysql实现按组区分后获取每组前几名的sql写法

本文链接: https://lsjlt.com/news/200475.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

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

目录

一、创建表，插入相关测试数据

二、查询每科成绩前三的记录

三、查询学生各科分数大于等于90分的记录

1. 第一种写法：正向思考

2. 第二种写法：逆向思考

总结

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

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

MySql分组后随机获取每组一条数据的操作

mysql分组排序取组内第一的数据行获取分组后，组内排名第一或最后的数据行。

SQL Server实现查询每个分组的前N条记录

Mysql分组排序取每组第一条的2种实现方式

用mysql实现按条件分组并且每组去时间最大的一条

mysql中实现按时间分组查询的方法

Mysql分组查询每组最新一条数据的三种实现方法

Java模拟rank/over函数实现获取分组排名的方法详解

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式