我通过从我的数据库中输入两个表来搜索学生的排名,首先我们有成绩表(id,marks,student_id,course_id),然后我们有比赛表(id,name,coef)来查找学生的平均值的总和(grades.marks*courses.coef),现在我们想知道每个学生的排名作为平均值的函数
下面是使用的请求
SELECT g1.student_id, SUM(g1.marks*c.coef) as averages1,
(SELECT COUNT(SUM(g2.marks*c2.coef))+1
FROM grades g2
JOIN courses c2 ON c2.id = g2.course_id
HAVING SUM(g1.marks*c.coef) < SUM(g2.marks*c2.coef)
) AS RANG
FROM grades g1
JOIN courses c ON c.id = g1.course_id
GROUP BY g1.student_id
ORDER BY averages1 DESC发布于 2021-07-09 18:43:07
如果您需要排名,请使用rank()函数:
SELECT g.student_id, SUM(g.marks * c.coef) as averages,
RANK() OVER (ORDER BY SUM(g.marks*c.coef) DESC) as ranking
FROM grades g JOIN
courses c
ON c.id = g1.course_id
GROUP BY g.student_id
ORDER BY averages DESChttps://stackoverflow.com/questions/68314856
复制相似问题