MySQL如何对一名学生的科目成绩进行排名/位置

Question

所以我有一个名为 MARKS 的表，我有这些列

STUDENT_ID, CLASSFORM_NAME, ACADEMIC_YEAR, TERM,  SUBJECT_NAME, TOTAL_MARKS

所以我想根据某个特定学生的科目名称、类(class)名称、学期和学术年份选择总分排名

这是我使用过但不起作用的查询

SELECT *
FROM (SELECT total_marks, @curRank := @curRank + 1 AS scorePosition
      FROM marks m, (SELECT @curRank := 0) q
      WHERE classform_name=? AND term=? AND academic_year=? AND subject_name=?
     ) t
WHERE student_id =?
ORDER BY total_marks DESC

帮帮我吧。谢谢。

Answer 1

在分配排名之前，您需要对数据进行排序。为了安全起见，请使用子查询:

SELECT m.*
FROM (SELECT m.*, @curRank := @curRank + 1 AS scorePosition
      FROM (SELECT m.*
            FROM marks m
            WHERE classform_name = ? AND term = ? AND academic_year = ? AND subject_name = ?
            ORDER BY total_marks DESC
           ) m CROSS JOIN
           (SELECT @curRank := 0) q
     ) m
WHERE m.student_id = ?;