怎么用sql语句对学生成绩进行排名?

一张学生成绩表assess_score,
有total_score(总成绩),id,student_name(姓名),student_number(学号),
assess_year(学年),根据总成绩进行成绩排名,需要考虑重复成绩和每个学年的成绩排名

这是我写的 请大佬帮我改一下

SELECT assess_year,student_number,id,SUM(courseScore),DENSE_RANK() OVER(ORDER BY SUM(courseScore) DESC ) as Ranking
            FROM course_score As courseScore group by student_number,assess_year,id

4个回答

SELECT
assess_year,
student_number,
id,
SUM( courseScore ),
DENSE_RANK() OVER( partition by student_number,
assess_year,
id ORDER BY SUM( courseScore ) DESC ) AS Ranking
FROM
course_score AS courseScore

SELECT
SUM( total_score ) score,
assess_year
FROM
course_score
GROUP BY
student_number,
assess_year
ORDER BY
score

order by 字段名就可以排序了啊

weixin_39946839
猪头,怪我喽 嗯嗯 也是
10 个月之前 回复
weixin_42081616
long丶api 因为这里有同分,所以必然会出现并列名次,所以光排序肯定不行
10 个月之前 回复

SELECT assess_year,student_number,id,SUM(courseScore),DENSE_RANK() OVER(ORDER BY SUM(courseScore) DESC ) as Ranking
FROM course_score As courseScore group by student_number,assess_year,id
order by total_score

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问