mysql成绩排名

有一分数表，和一学生信息表，通过学号关联，要实现 根据学号查询此学生在自己院系的各类别成绩排名

成绩表

 

学生表

 

sql实现

SELECT
    a.score,
    a.`name`,
    a.ability_type,
    t2.major,
    (
        SELECT
            count(DISTINCT (score))
        FROM
            t_major_ability AS b
            INNER JOIN t_student as t1 ON t1.`number` = b.`number`
        WHERE
            b.ability_type = a.ability_type
            AND t1.major = t2.major
            AND b.score > a.score) + 1 AS ran -- 查询出相同专业和相同类别比当前学号分数得分高的数量 + 1即为排名
    FROM
        t_major_ability AS a
        INNER JOIN t_student as t2 ON t2.`number` = a.`number`
    WHERE
        t2.`number`="202101001"
    ORDER BY
        ran;

效果