sql查询各科成绩前三名—-详述过程,思路清晰不烧脑

使用mysql、hive查询各科成绩前三名一、建表造数据二、使用myql查询2.1 不考虑并列情况方法一:使用加行号的方式查询方法二:使用子查询嵌套查询(使用最多)2.2 考虑并列情况三、使用hive查询前三名3.1 不考虑并列情况:rank()3.2 考虑并列情况:dense_rank()

一、建表造数据

建表:

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

二、使用myql查询
2.1 不考虑并列情况
方法一:使用加行号的方式查询

加行号的使用规则是:@rowNum:=num
意思是声明一个叫 rowNum 的变量并赋值为num
示例: 现在有一个需求是: “查询学生 java 课的成绩、姓名并排名?”

select score,name,@m1:=@m1+1 r from scores,(select @m1:=0)a where subject='java' order by score desc

查询结果如下
sql查询各科成绩前三名----详述过程,思路清晰不烧脑
此处加行号m1的作用就可以体现出来,查询语句中多了一个字段 r ,他可以以数字1,2,3,4,5的形式显示排名

由此引申,此条查询语句结尾在加上 limit 3 便可以取出前三名

select score,name,@m1:=@m1+1 r from scores,(select @m1:=0)a where subject='java' 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:=@m1+1 r from scores,(select @m1:=0)a where subject='java' order by score desc limit 3)s1
join
(select score,name,@m2:=@m2+1 r from scores,(select @m2:=0)b where subject='python' order by score desc limit 3)s2
on s1.r=s2.r;

输出结果为:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

这种方法查询实际上是 列转行 的方式,将字段subject 列 转成 行 输出。
优点 是容易理解,增加了一个字段显示排名,更加直观。
缺点 是在关联条件多(比如课程数量大于10,查询每科前10名,前20名成绩…)的情况下, join 关联10次以上,频繁的join会损耗系统很多性能,严重的会直接堵塞死。且有个弊端是写查询语句的时候必须要知道具体有几门课及课程名称,where 条件就已经限定了每门课的课程id或者课程名称,但有些情况下表数据量很大的时候,这种方法是不合适的。

方法二:使用子查询嵌套查询(使用最多)

select s1.* from scores s1
where (select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score)<3
order by s1.subject,s1.score desc;

查询结果如下:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

这种方法比较难懂,但是查询速度快且代码简单,解析如下:

首先如果要查询所有课程的成绩,如下:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑
再对各科成绩倒序排列输出:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑
然后 取各科成绩前三名,就需要嵌套子查询进行筛选,代码如下:

select s1.* from scores s1
where (select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score)<3
order by s1.subject,s1.score desc;

查询语句重点在于 -->子查询语句: select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score
意思是:统计学生个数,即关联两个分数表s1、s2,外层查询每查询一次,再到内层循环中查询表s2,当课程名相同时,统计 s1.score<s2.score 即表s2中成绩大于s1的人数有几人。
这么说可能还是有点懵,现在我们来走一遍流程,从头到尾依次遍历一次,详细说明:

首先从学生a开始查询:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

→学生b:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

→学生c:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

→学生d:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

→学生e:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

此时从学生a到e的 “java” 课程前三名已经筛选完成,对于 “python” 课程,重复上述流程即可
全部筛选完毕最后再对查询出的课程、分数倒序排列即可 :order by s1.subject,s1.score desc

2.2 考虑并列情况

select s1.name,s1.subject,s1.score from scores s1
left join (select distinct subject,score from scores) s2
on s1.subject=s2.subject
and s1.score<s2.score
group by s1.name,s1.subject,s1.score
having count(1)<3
order by subject,score desc;

查询结果如下:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑
很直观的可以看出,学生b和c成绩都为90分,并列第二名,学生e成绩80分,为第三名

查询语句解析:
这是在 2.1方法二 的基础上,使用 distinct 关键字对表s2中存在多名同学分数相同的情况进行 去重,从而达到并列排名的目的。需要注意的是,由于groub by 的条件是表s1中的字段,所以 count(1) 统计的是表s1中每次查询s1.score<s2.score 成绩低于表s2的学生人数,满足条件小于3,就可以取出前三名

三、使用hive查询前三名

使用hive查询需要调用 窗口函数,类似于 2.1方法一 中加行号查询,但使用不同的窗口函数可以实现并列与不并列的排名顺序

3.1 不考虑并列情况:rank()

select * from
(select name,subject,score,rank() over (partition by subject order by score desc) ranks from scores)s
where ranks<4;

查询结果如下:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

3.2 考虑并列情况:dense_rank()

select * from
(select name,subject,score,dense_rank() over (partition by subject order by score desc) ranks from scores)s
where ranks<4;

查询结果如下:
sql查询各科成绩前三名----详述过程,思路清晰不烧脑

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

sql查询各科成绩前三名----详述过程,思路清晰不烧脑

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » sql查询各科成绩前三名—-详述过程,思路清晰不烧脑
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏