如何在MySQL中进行分组排名

Question

所以我有一个表，如下所示：

ID_STUDENT | ID_CLASS | GRADE
-----------------------------
   1       |    1     |  90
   1       |    2     |  80
   2       |    1     |  99
   3       |    1     |  80
   4       |    1     |  70
   5       |    2     |  78
   6       |    2     |  90
   6       |    3     |  50
   7       |    3     |  90

然后我需要对它们进行分组、排序和排序，以给出：

ID_STUDENT | ID_CLASS | 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

现在我知道您可以使用temp变量来排序，like here，但是如何对分组的集合执行此操作呢？谢谢你的见解！

Answer 1

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 second.
2. @student和初始化为-1
3. @class用于测试是否输入下一个集合。如果id_class的前一个值(存储在@class中)不等于当前值(存储在id_class中)，则将@student置零。否则，is incremented.
4. @class将被赋予新值id_class，并将在下一行的步骤3测试中使用。

Answer 2

Quassnoi的解决方案(标记为最佳答案)存在问题。

我有同样的问题(即在MySQL中模拟SQL窗口函数)，我用来实现Quassnoi的解决方案，使用用户定义的变量来存储以前的行值……

但是，也许在MySQL升级或其他什么之后，我的查询不再起作用。这是因为不能保证SELECT中字段的求值顺序。@class分配可以在@student分配之前进行评估，即使它放在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()函数只是用来设置@class变量。然后，将left() (等于NULL)的结果连接到预期的结果是透明的。

不是很优雅，但它很有效！

Answer 3

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 |
+------------+----------+-------+------+