一、建表造数据
建表：
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
1
查询结果如下
此处加行号m1的作用就可以体现出来，查询语句中多了一个字段 r
由此引申，此条查询语句结尾在加上 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;
输出结果为：
这种方法查询实际上是 列转行 的方式，将字段subject 列 转成 行 输出。优点 是容易理解，增加了一个字段显示排名，更加直观。缺点 是在关联条件多(比如课程数量大于10，查询每科前10名，前20名成绩…)的情况下， join
方法二：使用子查询嵌套查询（使用最多）
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 count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score意思是：统计学生个数，即关联两个分数表s1、s2，外层查询每查询一次，再到内层循环中查询表s2，当课程名相同时，统计 s1.score<s2.score
首先从学生a开始查询：
→学生b：
→学生c：
→学生d：
→学生e：
此时从学生a到e的 “java” 课程前三名已经筛选完成，对于 “python”
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)
三、使用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;
查询结果如下：
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;
查询结果如下：
是不是非常简单！
