首页新闻找找看学习计划

SQL 数据库查询学生成绩总分排名

0
悬赏园豆:20 [已解决问题] 解决于 2019-03-27 08:16

我建立了3个表;分别为Students:(StudentNo、StudentName)、Subject:(SubjectID、SubjectName)、Result:(Result、SubjectID、StudentID)请教一下查询总分排名,排名那里怎么写?下面这个是我写的排名那里不会。

select '姓名'=a.StudentName,'总分'=SUM(Result),'排名'=??? from Students a inner join Result b on a.StudentNO=b.StudentID where StudentName in ('姓名') group by a.StudentNO,a.StudentName order by SUM(Result) desc

白菜园子呀的主页 白菜园子呀 | 初学一级 | 园豆:93
提问于:2019-03-25 20:00
< >
分享
最佳答案
0

SELECT A.StudentName AS 姓名, SUM(B.Result) AS 总分, ROW_NUMBER() OVER (ORDER BY (SUM(B.Result)) DESC) AS Ranking
FROM Students A
INNER JOIN Result B ON A.StudentNO=B.StudentID
GROUP BY A.StudentName

收获园豆:10
SiPenglei | 菜鸟二级 |园豆:216 | 2019-03-26 22:35
其他回答(2)
0

select '姓名'=a.StudentName,'总分'=SUM(Result),(select count(Result)+1 from Result where s.score>SUM) 排名 from Students a inner join Result b on a.StudentNO=b.StudentID where StudentName in ('姓名') group by a.StudentNO,a.StudentName order by SUM(Result) desc

收获园豆:4
一世红尘 | 园豆:321 (菜鸟二级) | 2019-03-25 22:45
0

用自增函数: '排名' =row_number() over(order by SUM(Result) desc)

收获园豆:6
三人乐乐 | 园豆:3150 (老鸟四级) | 2019-03-26 10:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册