sql查询各科成绩前三名----详述过程，思路清晰不烧脑
使⽤mysql、hive查询各科成绩前三名
⼀、建表造数据
建表：
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
查询结果如下
此处加⾏号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 limit3
再引申，若要查询所有课程的成绩，取前三名，则就需要将其他的课程表 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 limit3)s1
join
(select score,name,@m2:=@m2+1 r from scores,(select@m2:=0)b where subject='python'order by score desc limit3)s2
on s1.r=s2.r;
输出结果为：
这种⽅法查询实际上是 列转⾏ 的⽅式，将字段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;
查询结果如下：
这种⽅法⽐较难懂，但是查询速度快且代码简单，解析如下：
⾸先如果要查询所有课程的成绩，如下：
再对各科成绩倒序排列输出：
然后 取各科成绩前三名，就需要嵌套⼦查询进⾏筛选，代码如下：
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 distinct from查询语句重点在于 -->⼦查询语句： 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开始查询：
→学⽣c：
→学⽣d：
此时从学⽣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;
查询结果如下：
很直观的可以看出，学⽣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()

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。