本文主要是介绍Mysql 8.0+ 窗口函数 练习题(持续补充中)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
建表语句
create table student_scores
(id int auto_incrementprimary 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 sumfrom (select *, (score > avg_score) bigfrom (select *, avg(score) over (partition by class,subject) as avg_scorefrom 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 |
+--+-----+----+-------+-----+---------+---+---+这篇关于Mysql 8.0+ 窗口函数 练习题(持续补充中)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
