SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, id_student
  ) t

这是一种非常简单的方式：
1.初始查询的顺序为先id_class，后id_student。
1.将@student和@class初始化为-1

1. @class用于测试是否输入了下一个集合。如果id_class的前一个值（存储在@class中）不等于当前值（存储在id_class中），则@student清零。否则递增。
2. @class会被指派新的值id_class，而且它会在下一列的步骤3的测试中使用。

Quassnoi的解决方案存在问题（标记为最佳答案）。
我有同样的问题（即模拟MySQL中的SQL窗口函数），我曾经实现Quassnoi的解决方案，使用用户定义的变量来存储前一行的值...
但是，也许在MySQL升级或其他什么之后，我的查询不再起作用了。这是因为SELECT中字段的求值顺序不能保证。@class assignment可以在@student assignment之前求值，即使它在SELECT中被放在后面。
MySQL文档中对此进行了说明，如下所示：
一般来说，您不应该在同一个陈述式中，将值指派给使用者变数并读取该值。您可能会得到预期的结果，但这并不保证。包含使用者变数之运算式的评估顺序是未定义的，而且可能会根据指定陈述式中包含的元素而变更。此外，不保证MySQL服务器的各个版本之间的顺序相同。
来源：http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
最后，我使用了一个类似的技巧，确保在阅读后指定@class：

SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN concat(left(@class:=id_class, 0), 0) ELSE @student+1 END AS rn
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, grade desc
  ) t

使用left（）函数只是用来设置@类变量.然后，将left（）的结果（等于NULL）连接到预期的结果是透明的。
不是很优雅，但它的工作！

SELECT g1.student_id
     , g1.class_id
     , g1.grade
     , COUNT(*) AS rank
  FROM grades   AS g1
  JOIN grades   AS g2
    ON (g2.grade, g2.student_id) >= (g1.grade, g1.student_id)
   AND g1.class_id = g2.class_id
 GROUP BY g1.student_id
        , g1.class_id
        , g1.grade
 ORDER BY g1.class_id
        , rank
 ;

结果：

+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
|          2 |        1 |    99 |    1 |
|          1 |        1 |    90 |    2 |
|          3 |        1 |    80 |    3 |
|          4 |        1 |    70 |    4 |
|          6 |        2 |    90 |    1 |
|          1 |        2 |    80 |    2 |
|          5 |        2 |    78 |    3 |
|          7 |        3 |    90 |    1 |
|          6 |        3 |    50 |    2 |
+------------+----------+-------+------+

从上面修改，这工程，但它比我认为它需要更复杂：

SELECT ID_STUDENT, ID_CLASS, GRADE, RANK
FROM
    (SELECT ID_STUDENT, ID_CLASS, GRADE,
        @student:=CASE WHEN @class <> id_class THEN 1 ELSE @student+1 END AS RANK,
        @class:=id_class AS CLASS
    FROM
        (SELECT @student:= 0) AS s,
        (SELECT @class:= 0) AS c,
        (SELECT * 
            FROM Students
            ORDER BY ID_CLASS, GRADE DESC
        ) AS temp
    ) AS temp2

虽然我没有足够的信誉点来评论（有点幽默），但MySQL在最近几年已经取得了很大的进步。添加了窗口函数和CTE（WITH子句），这意味着现在支持rank（和row_number等）。
我是同一个“乔恩阿姆斯特朗- Xgc”，但该帐户已失去了旧的电子邮件地址的风。
一条评论提出了一个问题，即MySQL是否支持排名窗口函数。是的，我知道
几年前，我最初的React是：

SELECT p1.student_id
     , p1.class_id
     , p1.grade
     , COUNT(p2.student_id) AS rank
  FROM grades   AS p1
  JOIN grades   AS p2
    ON (p2.grade, p2.student_id) >= (p1.grade, p1.student_id)
   AND p1.class_id = p2.class_id
 GROUP BY p1.student_id, p1.class_id
 ORDER BY p1.class_id, rank
;

结果：

+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
|          2 |        1 |    99 |    1 |
|          1 |        1 |    90 |    2 |
|          3 |        1 |    80 |    3 |
|          4 |        1 |    70 |    4 |
|          6 |        2 |    90 |    1 |
|          1 |        2 |    80 |    2 |
|          5 |        2 |    78 |    3 |
|          7 |        3 |    90 |    1 |
|          6 |        3 |    50 |    2 |
+------------+----------+-------+------+
9 rows in set (0.001 sec)

使用ROW_NUMBER窗口函数：

WITH cte1 AS (
        SELECT student_id
             , class_id
             , grade
             , ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
          FROM grades
     )
SELECT *
  FROM cte1
 ORDER BY class_id, r
;

结果：

+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
|          2 |        1 |    99 |    1 |
|          1 |        1 |    90 |    2 |
|          3 |        1 |    80 |    3 |
|          4 |        1 |    70 |    4 |
|          6 |        2 |    90 |    1 |
|          1 |        2 |    80 |    2 |
|          5 |        2 |    78 |    3 |
|          7 |        3 |    90 |    1 |
|          6 |        3 |    50 |    2 |
+------------+----------+-------+------+
9 rows in set (0.002 sec)

使用RANK窗口函数：

WITH cte1 AS (
        SELECT student_id
             , class_id
             , grade
             , RANK() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
          FROM grades
     )
SELECT *
  FROM cte1
 ORDER BY class_id, rank
;

结果：

+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
|          2 |        1 |    99 |    1 |
|          1 |        1 |    90 |    2 |
|          3 |        1 |    80 |    3 |
|          4 |        1 |    70 |    4 |
|          6 |        2 |    90 |    1 |
|          1 |        2 |    80 |    2 |
|          5 |        2 |    78 |    3 |
|          7 |        3 |    90 |    1 |
|          6 |        3 |    50 |    2 |
+------------+----------+-------+------+
9 rows in set (0.000 sec)

SELECT ID_STUDENT, ID_CLASS, GRADE, RANK() OVER(
PARTITION BY ID_CLASS
ORDER BY GRADE ASC) AS 'Rank'
FROM table
ORDER BY ID_CLASS;

我在一次家庭作业中遇到了类似的问题，发现MySQL（不能代表任何其他RDBMS）的RANK（）方法有一个分区参数。

我做了一番搜寻，找到了这篇文章才想出这样的解决办法：

SELECT S2.*, 
FIND_IN_SET(
S2.GRADE
, (
SELECT GROUP_CONCAT(GRADE ORDER BY GRADE DESC)
FROM Students S1
WHERE S1.ID_CLASS = S2.ID_CLASS
)
) AS RANK
FROM Students S2 ORDER BY ID_CLASS, GRADE DESC;

你觉得哪个更好？

rank（）over（按class_id划分，按级别说明排序）如何？https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/