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
最佳答案
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
其他回答(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
0
用自增函数： '排名' =row_number() over(order by SUM(Result) desc)
收获园豆：6
