mysql - 如何在mysql上按年份和学期排名？

Question

我的数据库(MySQL)表中有数据

SELECT * FROM `student_result_group`

结果

我试试:

SELECT year, term, grade_id, student_id, total_exam1, total_exam2, total,
       FIND_IN_SET( total, ( SELECT GROUP_CONCAT( total ORDER BY total DESC ) FROM student_result_group)) AS rank
from student_result_group
GROUP BY year, term, grade_id, student_id

我得到这样的结果:

我想要这个结果:

我应该编写哪个查询来获取每个学期和每年的排名？

感谢您的帮助。

Answer 1

只是这个查询:

    select
    s1.year, 
    s1.term, 
    s1.student_id, 
    s1.grade_id, 
    s1.total_exam1, 
    s1.total_exam2, 
    s1.total,
    count(s2.total)+1 AS rank
from
    student_result_group s1 LEFT JOIN student_result_group s2
    ON s1.grade_id = s2.grade_id AND s1.student_id != s2.student_id AND s1.term = s2.term AND s1.year = s2.year AND s1.total <= s2.total
GROUP by
    s1.student_id,
    s1.year,
    s1.term,
    s1.grade_id
ORDER BY
    s1.year, s1.term, s1.total, s1.student_id