网站建设相关,cento安装wordpress,成都兼职建设网站,淘宝禁止了网站建设类提示#xff1a;文章写完后#xff0c;目录可以自动生成#xff0c;如何生成可参考右边的帮助文档 文章目录 sql查询各科成绩前三名建表造数 方法一#xff1a;使用加行号的方式查询方法二#xff1a;使用子查询嵌套查询原理解析考虑并列情况 方法三#xff1a;窗口函数1… 提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档 文章目录 sql查询各科成绩前三名建表造数 方法一使用加行号的方式查询方法二使用子查询嵌套查询原理解析考虑并列情况 方法三窗口函数1.ROW_NUMBER2. 不考虑并列情况:rank()3.考虑并列情况:dense_rank() sql查询各科成绩前三名
建表
create table scores(
name varchar(100),
subject varchar(100),
score int
);造数
insert into scores values
(学生a,java,100),
(学生b,java,90),
(学生c,java,90),
(学生d,java,60),
(学生e,java,80),
(学生a,python,100),
(学生b,python,90),
(学生c,python,90),
(学生d,python,60),
(学生e,python,80); 方法一使用加行号的方式查询
不考虑并列情况
select score,name,m1:m11 r from scores,(select m1:0)a where subjectjava order by score desc
查询结果如下 此处加行号m1的作用就可以体现出来查询语句中多了一个字段 r 他可以以数字1,2,3,4,5的形式显示排名
由此引申此条查询语句结尾在加上 limit 3 便可以取出前三名
select score,name,m1:m11 r from scores,(select m1:0)a where subjectjava order by score desc limit 3
再引申若要查询所有课程的成绩取前三名则就需要将其他的课程表 join 在一起关联条件为每条查询语句的行号相等
select s1.score java成绩,s1.name,s2.score python成绩,s2.name,s1.r 排名 from
(select score,name,m1:m11 r from scores,(select m1:0)a where subjectjava order by score desc limit 3)s1
join
(select score,name,m2:m21 r from scores,(select m2:0)b where subjectpython order by score desc limit 3)s2
on s1.rs2.r; 这种方法查询实际上是 列转行 的方式将字段subject 列 转成 行 输出。
优点 是容易理解增加了一个字段显示排名更加直观。缺点 是在关联条件多(比如课程数量大于10查询每科前10名前20名成绩…)的情况下 join 关联10次以上频繁的join会损耗系统很多性能严重的会直接堵塞死。且有个弊端是写查询语句的时候必须要知道具体有几门课及课程名称where条件就已经限定了每门课的课程id或者课程名称但有些情况下表数据量很大的时候这种方法是不合适的。
方法二使用子查询嵌套查询
SELECT s1.* FROM scores s1
WHERE (SELECT COUNT(1) FROM scores s2 WHERE s1.subjects2.subject AND s1.scores2.score)3
ORDER BY s1.subject,s1.score DESC; 原理解析
意思是统计学生个数即关联两个分数表s1、s2 外层查询每查询一次再到内层循环中查询表s2
外层表首先查询出一条数据再到内层循环中查询表s2当课程名相同时统计 s1.score s2.score 的个数如果超过3个证明此时外层的这一行数据的 score 不是此课程的前三名。因为前三名 全班的成绩不应该有超过三个人 比他的分数还高最多可能是第一名 和第二名比分数高如果满足s1.scores2.score) 3 就是目标前三名的数据 保留下来 不满足就被where条件过滤掉外层下一条数据 继续循环
首先从学生a开始查询 此时从学生a到e的 “java” 课程前三名已经筛选完成对于 “python” 课程重复上述流程即可
全部筛选完毕最后再对查询出的课程、分数倒序排列即可 order by s1.subject,s1.score desc
考虑并列情况
elect s1.name,s1.subject,s1.score from scores s1left join (select distinct subject,score from scores) s2on s1.subjects2.subjectand s1.scoregroup by s1.name,s1.subject,s1.scorehaving count(1)3order by subject,score desc;很直观的可以看出学生b和c成绩都为90分并列第二名学生e成绩80分为第三名
查询语句解析
这是在 2.1方法二 的基础上使用 distinct 关键字对表s2中存在多名同学分数相同的情况进行去重从而达到并列排名的目的。需要注意的是由于groub by 的条件是表s1中的字段所以 count(1)统计的是表s1中每次查询s1.score
方法三窗口函数
mysql8 或者 HIVE 才支持窗口函数 MySQL基础–10—MySQL8新特性----窗口函数 1.ROW_NUMBER SELECT * FROM
(SELECT NAME,SUBJECT,score,ROW_NUMBER() over (PARTITION BY SUBJECT ORDER BY score DESC) ranks FROM scores)s
WHERE ranks4; 2. 不考虑并列情况:rank() SELECT * FROM
(SELECT NAME,SUBJECT,score,rank() over (PARTITION BY SUBJECT ORDER BY score DESC) ranks FROM scores)s
WHERE ranks4; 3.考虑并列情况:dense_rank() SELECT * FROM
(SELECT NAME,SUBJECT,score,dense_rank() over (PARTITION BY SUBJECT ORDER BY score DESC) ranks FROM scores)s
WHERE ranks4;
