查询各科分数最高的科目名称，学生学号，姓名，分数，排名

查询各科分数最高的科目名称，学生学号，姓名，分数，排名

1.有三个表student(学生表),sc(成绩表),course(课程表)，其定义和数据如下

student：

sid	sname	sage	ssex
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-12-20	男
04	李云	1990-12-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-01-01	女
07	郑竹	1989-01-01	女
09	张三	2017-12-20	女
10	李四	2017-12-25	女
11	李四	2012-06-06	女
12	赵六	2013-06-13	女
13	孙七	2014-06-01	女

sc:

sid	cid	score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
04	04	60
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

course:

cid	cname	tid
01	语文	02
02	数学	01
03	英语	03
04	生理学	04

解题思路：

1.首先给sc表按照cid分区按照分数降序排序，最后加个排名

select cid,sid,score,row_number() over (partition by CId order by score desc) ranks 
from sc

这里不会row_number() over()函数的同学自行百度，搞清楚rank()，dense_rank()，row_number()的区别，以及配上over()函数的用法。

查询结果为：

2.然后和student表、course表连接起来，如果有多个最高分，按照名字的先后顺序排序。

select sc.cid,c.cname,sc.sid,s.sname,sc.score,row_number() over (partition by sc.CId order by sc.score desc,s.sname) ranks 
from sc,student s,course c
where s.sid = sc.sid and sc.cid=c.cid

查询的结果为：

3.要选取每科的最高分，那么我们把上面查询的结果看做一个表，查询条件为ranks=1，那么就是选取的每科的最高分，如果说题目是选取每科的前3名，我们只要加条件ranks<=3就可以了，我们来看代码。

select *
from(
	select sc.cid,c.cname,sc.sid,s.sname,sc.score,row_number() over (partition by sc.CId order by sc.score desc,s.sname) ranks 
	from sc,student s,course c
	where s.sid = sc.sid and sc.cid=c.cid
)a
where ranks=1

查询结果：

结果没问题，我们来看看每科前3的sql和查询结果。

select *
from(
 select sc.cid,c.cname,sc.sid,s.sname,sc.score,row_number() over (partition by sc.CId order by sc.score desc,s.sname) ranks 
 from sc,student s,course c
 where s.sid = sc.sid and sc.cid=c.cid
)a
where ranks<=3

查询结果：

结果没毛病，如果大家有更好的方法，或者更高效的方法可以一起讨论，欢迎指正。