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

Question

我编写了以下 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'的学生的排名？

Answer 1

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"