mysql - 如何根据分数的总和获得特定用户的排名？

我编写以下MySQL命令来获取学生的分数总和，并对他们进行相应排名。

SELECT student_id, thescore, @rownum:=@rownum + 1 AS rankstudent
FROM
(
    SELECT student_id, SUM(score) AS thescore
    FROM school_ranking
    WHERE school like '%Standard6%' and student_id <> ''
    GROUP BY student_id
    ORDER BY thescore DESC

) Sub1
CROSS JOIN (SELECT @rownum:=0) Sub2

结果如下：

student_id   thescore    rankstudent
J007766      5739            1
J007625      5159            2
J007629      5158            3
J007713      4460            4
J007690      4384            5

我的问题是如何获得特定学生的排名？
例如，如何获取ID为“ J007625”的学生的排名？

最佳答案

SELECT rankstudent FROM (
SELECT student_id, thescore, @rownum:=@rownum + 1 AS rankstudent
FROM
(
    SELECT student_id, SUM(score) AS thescore
    FROM school_ranking
    WHERE school like '%Standard6%' and student_id <> ''
    GROUP BY student_id
    ORDER BY thescore DESC

) Sub1
CROSS JOIN (SELECT @rownum:=0) Sub2
) Sub3
WHERE `Sub3`.`student_id` = "J007625"

关于mysql - 如何根据分数的总和获得特定用户的排名？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37358370/