Question

sql查询某个学生的平均成绩的排名

数据表SC(Sno,Cno,Grade) //Sno为学号，Cno为课程号，Grade为成绩
数据：101 1 90
101 2 80
201 2 70
201 3 80
201 5 60
301 1 50
401 5 90
401 6 100
请问怎么才能用SQL语句求出学号为101的学生的平均成绩的排名？万分感谢！

Answer 1

1
2
3
4
5
6
7
8
9
10
11

select s.dname,s.sname,s.avggrade
from
(select t.dname,t.sname,t.avggrade,row_number() over (partition by t.dname order by t.avggrade desc) rn
from
(select a.name dname,b.name sname,avg(c.grade) avggrade
from
department a,student b,takes c
where a.department_id=b.department_id
and b.student_id=c.student_id
group by a.name,b.name) t) s
where s.rn=1

包含并列的情况，如果一个学院有两个第一，两个人都显示，以上为 sqlserver或oracle写法，其他数据库执行不了
-------------补充-----------
给你个通用的

select s2.*
from
(select t.dname,max(t.avggrade) avggrade
from
(select a.name dname,b.name sname,avg(c.grade) avggrade
from
department a,student b,takes c
where a.department_id=b.department_id
and b.student_id=c.student_id
group by a.name,b.name) t
group by t.dname) s1,
(select a.name dname,b.name sname,avg(c.grade) avggrade
from
department a,student b,takes c
where a.department_id=b.department_id
and b.student_id=c.student_id
group by a.name,b.name) s2
where s1.dname=s2.dname
and s1.avggrade=s2.avggrade