Mysql 8.0+ 窗口函数 练习题(持续补充中)

最新推荐文章于 2024-09-02 14:48:18 发布
最新推荐文章于 2024-09-02 14:48:18 发布
阅读量230 收藏
点赞数 2
文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45876930/article/details/139152769
版权

建表语句

create table student_scores
(
    id      int auto_increment
        primary key,
    class   varchar(50) null,
    name    varchar(50) null,
    subject varchar(50) null,
    score   int         null
);

模拟数据

INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (1, '初一1班', '赵晨', '英语', 78);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (2, '初一1班', '钱进', '英语', 82);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (3, '初一1班', '孙浩', '英语', 85);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (4, '初一1班', '李想', '英语', 80);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (5, '初一1班', '周梅', '英语', 75);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (6, '初一2班', '吴晶', '英语', 88);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (7, '初一2班', '郑丽', '英语', 92);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (8, '初一2班', '王磊', '英语', 84);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (9, '初一2班', '冯程', '英语', 79);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (10, '初一2班', '陈思', '英语', 81);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (11, '初一3班', '林涛', '英语', 90);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (12, '初一3班', '罗阳', '英语', 85);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (13, '初一3班', '宋文', '英语', 83);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (14, '初一3班', '梁静', '英语', 88);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (15, '初一3班', '韩雪', '英语', 82);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (16, '初一1班', '赵晨', '物理', 76);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (17, '初一1班', '钱进', '物理', 80);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (18, '初一1班', '孙浩', '物理', 85);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (19, '初一1班', '李想', '物理', 90);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (20, '初一1班', '周梅', '物理', 95);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (21, '初一2班', '吴晶', '物理', 87);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (22, '初一2班', '郑丽', '物理', 83);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (23, '初一2班', '王磊', '物理', 79);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (24, '初一2班', '冯程', '物理', 81);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (25, '初一2班', '陈思', '物理', 78);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (26, '初一3班', '林涛', '物理', 82);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (27, '初一3班', '罗阳', '物理', 88);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (28, '初一3班', '宋文', '物理', 84);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (29, '初一3班', '梁静', '物理', 86);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (30, '初一3班', '韩雪', '物理', 80);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (31, '初一1班', '张伟', '数学', 73);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (32, '初一2班', '王芳', '数学', 79);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (33, '初一1班', '张伟', '数学', 73);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (34, '初一2班', '王芳', '数学', 79);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (35, '初一2班', '李娜', '数学', 75);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (36, '初一3班', '赵强', '数学', 80);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (37, '初一1班', '刘畅', '语文', 82);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (38, '初一1班', '张伟', '语文', 78);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (39, '初一2班', '王芳', '语文', 84);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (40, '初一2班', '李娜', '语文', 80);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (41, '初一3班', '赵强', '语文', 85);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (42, '初一1班', '刘畅', '英语', 88);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (43, '初一1班', '张伟', '英语', 82);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (44, '初一2班', '王芳', '英语', 79);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (45, '初一2班', '李娜', '英语', 75);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (46, '初一3班', '赵强', '英语', 90);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (47, '初一1班', '刘畅', '物理', 85);
INSERT INTO `table`.student_scores (id, class, name, subject, score) VALUES (48, '初一1班', '张伟', '物理', 80);
问题1：求出每个学生成绩最高的三条记录
-- 答案SQL：
select *
from (select *, row_number() over (partition by name order by score desc) as seq from student_scores) temp
where seq <= 3

结果：
+--+-----+----+-------+-----+---+
|id|class|name|subject|score|seq|
+--+-----+----+-------+-----+---+
|24|初一2班 |冯程  |物理     |81   |1  |
|9 |初一2班 |冯程  |英语     |79   |2  |
|42|初一1班 |刘畅  |英语     |88   |1  |
|47|初一1班 |刘畅  |物理     |85   |2  |
|37|初一1班 |刘畅  |语文     |82   |3  |
|6 |初一2班 |吴晶  |英语     |88   |1  |
|21|初一2班 |吴晶  |物理     |87   |2  |
|20|初一1班 |周梅  |物理     |95   |1  |
|5 |初一1班 |周梅  |英语     |75   |2  |
|3 |初一1班 |孙浩  |英语     |85   |1  |
|18|初一1班 |孙浩  |物理     |85   |2  |
|28|初一3班 |宋文  |物理     |84   |1  |
|13|初一3班 |宋文  |英语     |83   |2  |
|43|初一1班 |张伟  |英语     |82   |1  |
|48|初一1班 |张伟  |物理     |80   |2  |
|38|初一1班 |张伟  |语文     |78   |3  |
|40|初一2班 |李娜  |语文     |80   |1  |
|45|初一2班 |李娜  |英语     |75   |2  |
|35|初一2班 |李娜  |数学     |75   |3  |
|19|初一1班 |李想  |物理     |90   |1  |
|4 |初一1班 |李想  |英语     |80   |2  |
|11|初一3班 |林涛  |英语     |90   |1  |
|26|初一3班 |林涛  |物理     |82   |2  |
|14|初一3班 |梁静  |英语     |88   |1  |
|29|初一3班 |梁静  |物理     |86   |2  |
|8 |初一2班 |王磊  |英语     |84   |1  |
|23|初一2班 |王磊  |物理     |79   |2  |
|39|初一2班 |王芳  |语文     |84   |1  |
|44|初一2班 |王芳  |英语     |79   |2  |
|32|初一2班 |王芳  |数学     |79   |3  |
|27|初一3班 |罗阳  |物理     |88   |1  |
|12|初一3班 |罗阳  |英语     |85   |2  |
|46|初一3班 |赵强  |英语     |90   |1  |
|41|初一3班 |赵强  |语文     |85   |2  |
|36|初一3班 |赵强  |数学     |80   |3  |
|1 |初一1班 |赵晨  |英语     |78   |1  |
|16|初一1班 |赵晨  |物理     |76   |2  |
|7 |初一2班 |郑丽  |英语     |92   |1  |
|22|初一2班 |郑丽  |物理     |83   |2  |
|2 |初一1班 |钱进  |英语     |82   |1  |
|17|初一1班 |钱进  |物理     |80   |2  |
|10|初一2班 |陈思  |英语     |81   |1  |
|25|初一2班 |陈思  |物理     |78   |2  |
|15|初一3班 |韩雪  |英语     |82   |1  |
|30|初一3班 |韩雪  |物理     |80   |2  |
+--+-----+----+-------+-----+---+
问题2：找出每门课程都高于班级课程平均分的学生
select * from (select *, sum(big) over (partition by name) as sum
               from (select *, (score > avg_score) big
                     from (select *, avg(score) over (partition by class,subject) as avg_score
                           from student_scores) temp) tp) t
where sum = (select count(1) from student_scores where name = t.name)

结果：
+--+-----+----+-------+-----+---------+---+---+
|id|class|name|subject|score|avg_score|big|sum|
+--+-----+----+-------+-----+---------+---+---+
|47|初一1班 |刘畅  |物理     |85   |84.4286  |1  |3  |
|42|初一1班 |刘畅  |英语     |88   |81.4286  |1  |3  |
|37|初一1班 |刘畅  |语文     |82   |80.0000  |1  |3  |
|21|初一2班 |吴晶  |物理     |87   |81.6000  |1  |2  |
|6 |初一2班 |吴晶  |英语     |88   |82.5714  |1  |2  |
|18|初一1班 |孙浩  |物理     |85   |84.4286  |1  |2  |
|3 |初一1班 |孙浩  |英语     |85   |81.4286  |1  |2  |
|14|初一3班 |梁静  |英语     |88   |86.3333  |1  |2  |
|29|初一3班 |梁静  |物理     |86   |84.0000  |1  |2  |
|7 |初一2班 |郑丽  |英语     |92   |82.5714  |1  |2  |
|22|初一2班 |郑丽  |物理     |83   |81.6000  |1  |2  |
+--+-----+----+-------+-----+---------+---+---+
风很大很大
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql8 中的rank_Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数
weixin_30311179的博客
02-05 904
在低于Mysql8.0之前的版本中，要对序列进行排序的话需要用到自定义参数@，但是8.0+版本中加入了oracle中的窗口函数，rank()、row_num()、dense_rank()等函数。下面简单举几个例子介绍下这几个函数的应用。测试用的表主要有两个，一个是学生信息表student，二是学生分数表sc，具体的测试数据的mysql插入语句在另一篇博客：Mysql经典练习题与知识点总结中，大家可...
Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数
阿斯达克
04-02 2249
在低于Mysql8.0之前的版本中，要对序列进行排序的话需要用到自定义参数@，但是8.0+版本中加入了oracle中的窗口函数，rank()、row_num()、dense_rank()等函数。 下面简单举几个例子介绍下这几个函数的应用。测试用的表主要有两个，一个是学生信息表student，二是学生分数表sc，具体的测试数据的mysql插入语句在另一篇博客：Mysql经典练习题与知识点总结中，大家...
MySQL三种开窗函数详细用法，图文详解
baomingshu的博客
03-25 2967
over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。所以sum(score) over(partition by name ) ，就是先根据 name 分组(如图)，当前面加了sum（score）后就把根据name分组后的，每个（组）窗口里面的字段 score进行求和操作。rank()是排名，也为每一组的行生成一个序号,如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。
Mysql8.0习题系列（八）：窗口函数（一篇学会rank、dense_rank、row_number使用，超详细~）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-31 1544
本文给出了mysql8.0新支持的窗口函数之排序函数习题，主要区分了rank()、dense_rank()、row_number()三者之间的区别。
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
mysql 8.0 ocp 题库
06-10
MySQL 8.0 OCP（Oracle Certified Professional）是Oracle公司为MySQL数据库管理员提供的一项专业认证，旨在验证其在MySQL 8.0版本中的管理和优化技能。这个题库包含了准备OCP考试所需的各种试题，帮助考生熟悉考试...
mysql 8.0 宋红康 的窗口函数练习题
08-30
宋红康的窗口函数练习题可能包括以下的例子和练习： 1. 计算每个部门的平均工资和最高工资。 示例答案： SELECT department_id, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary, MAX(salary) OVER ...
MySQL数据库增删查改（基础）CRUD
chendemingxxx的博客
09-02 564
比如说：创建一张学生表，有姓名，学号。插入两个学生。可能有点草率看看例子： 顺序不是问题，后面参数和前面括号参数对等就行。比如说上面的例子：把id和name参数倒过来了，后面参数一一对应也可以。不推荐使用全列查询原因：比如说上面例子我只要id或者说只要name；当然也可以查询多个列： 比如说我们创建一个这样的表命令：第一个 效果：第三个总分效果：拓展一个现象： 拓展：语法：比较运算符：逻辑运算符：解释：%在mysql这里代表的是多个任意字符，_代表的是 一个任意字符。揭晓来才是修改硬盘里面的内容。案例：
mysql-day02
m0_64370841的博客
08-29 2232
【代码】mysql-day02。
MySQL 函数、约束、多表查询与事务详解
A_cot的博客
08-30 1655
在 MySQL 数据库中，函数、约束、多表查询和事务是非常重要的概念，它们可以帮助我们更好地管理和操作数据。本文将详细介绍这些概念，并通过代码演示来帮助你更好地理解。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 1337
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 1259
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
MySQL系列—7.内存结构
最新发布
md54333的博客
09-02 497
对于聚集索引页，DML操作带来的数据磁盘读取和缓存修改往往是顺序的、集中的，这就很好 地利用了聚集索引的优点，但随着聚集索引页的顺序IO，各个二级索引页的读取和修改通常是随 机IO的。而Change buffer将分散的、未缓存的二级索引页的变化进行缓存而非直接从磁盘中读 取索引页，待到其他操作需要访问二级索引时，只需将change buffer中的变化缓存合并然后加 入缓冲池(buffer pool)就行了，这样就大大地减少了磁盘随机读取的IO。可以控制change buffer只对部分类操作生效。
MySQL5.7.36之主从复制延迟复制-centos7
龙哥·三年风水从2011年开始做IT工作，从ps画图到前端开发->后端开发->框架开发->业务架构设计及开发->业务需求整理、开发->技术经理->技术总监
08-31 622
MySQL5.7.36之主从复制延迟复制-centos7
MySQL 使用C语言链接
JK01WYX的博客
09-02 1288
mysql_get_client_info() ; msql_real_connect() ; mysql_query() ; mysql_store_result() ; mysql_num_rows() ; mysql_num_fields() ; mysql_fetch_fields() ; mysql_fetch_fields() ; mysql_close()
docker里装mysql
m0_74744788的博客
08-31 282
: 你已经将容器的 3306 端口映射到宿主机的 3306 端口，这是正确的配置，允许你从宿主机访问 MySQL 服务。-v mysql:/var/lib/mysql 挂载点。--name mysql 容器名字。dd3b2a5dcb48 镜像id。拉取mysql镜像后。
解决数据插入到mysql时，多出一秒的问题
Gogym的博客
09-02 245
对象获取的时间是以带着小数的毫秒数传入数据库的，而当数据库datetime设置长度为0时，会自动根据毫秒数后面的小数进行四舍五入，会导致时间精度的缺失。当把数据库字段的长度设置为3时，数据库则会保留毫秒数的三位小数，则不会导致时间精度的丧失。比如：当创建时间是2020-04-25 22:30:50，插入mysql，发现时间为2020-04-25 22:30:51。当创建时间是2020-04-25 22:30:50.771，毫秒被四舍五入为2020-04-25 22:30:51。
xampp安装federated插件，实现mysql数据同步
阿拉丁的知识分享库
08-30 448
在a服务器上的data表创建触发器，data表插入数据后执行触发器插入到a服务器上data1。此时查看b服务器上的data表，数据已同步过来。需求：a服务器上的mysql数据库data表插入新数据时，需要同步到b服务器上的data表中。解决：a服务器上开启federated引擎插件，创建data1对应b服务器上的data表。把安装目录下MariaDB 10.4\lib\plugin的。复制到xampp\mysql\lib\plugin目录下。在a服务器data表增加触发器。1、查看mysql版本。
mysql 8.0+获取序列函数
06-06
MySQL 8.0及以上版本并没有内置的序列函数，但可以通过自定义函数实现类似的功能。以下是一个简单的自定义函数实现序列的示例： 1. 创建自定义函数 ``` DELIMITER $$ CREATE FUNCTION seq_nextval(seq_name VARCHAR(50)) RETURNS BIGINT BEGIN DECLARE seq_val BIGINT DEFAULT 0; SET seq_val = (SELECT seq_val FROM sequence WHERE seq_name = seq_name FOR UPDATE); IF seq_val IS NULL THEN SET seq_val = 1; INSERT INTO sequence (seq_name, seq_val) VALUES (seq_name, seq_val); ELSE SET seq_val = seq_val + 1; UPDATE sequence SET seq_val = seq_val WHERE seq_name = seq_name; END IF; RETURN seq_val; END$$ DELIMITER ; ``` 上述代码中，我们创建了一个名为 seq_nextval 的自定义函数，它接受一个参数 seq_name，表示序列的名称。函数的作用是获取指定名称的序列的下一个值。 函数首先从名为 sequence 的表中获取指定名称的序列的当前值，如果该序列不存在，则将其初始化为1，并返回1。如果序列存在，则将其值加1，并更新 sequence 表中的值，最后返回更新后的值。 2. 创建序列表 在自定义函数中，我们用到了一个名为 sequence 的表，它用于存储所有序列的当前值。我们需要先创建该表： ``` CREATE TABLE sequence ( seq_name VARCHAR(50) PRIMARY KEY, seq_val BIGINT ); ``` 3. 使用自定义函数获取序列的下一个值 现在我们可以使用 seq_nextval 函数获取指定名称的序列的下一个值了： ``` SELECT seq_nextval('my_seq'); ``` 上述代码中，我们调用 seq_nextval 函数，并传入名称为 my_seq 的序列。函数会返回该序列的下一个值。 需要注意的是，由于自定义函数中用到了事务和行级锁，因此在高并发场景下可能会出现性能问题。如果需要生成大量的序列，建议使用自增主键或其他更高效的方法。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值