如何在 MySQL 中执行分组排名

2021-11-20 00:00:00 sql window-functions mysql

所以我有一个表格如下:

So I have a table as follows:

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

然后我需要对它们进行分组、排序和排序:

I need to then group, sort and order them to give:

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

现在我知道你可以使用临时变量来进行排名，像这里，但我如何为分组集做这件事?感谢您的任何见解！

Now I know that you can use a temp variable to rank, like here, but how do I do it for a grouped set? Thanks for any insight!

推荐答案

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 第二排序.
  2. @student 和 @class 被初始化为 -1
  3. @class 用于测试是否输入了下一组.如果 id_class 的先前值(存储在 @class 中)不等于当前值(存储在 id_class 中)，@student 被归零.否则递增.
  4. @class 被赋值为 id_class 的新值，它将在下一行的第 3 步的测试中使用.
  1. Initial query is ordered by id_class first, id_student second.
  2. @student and @class are initialized to -1
  3. @class is used to test if the next set is entered. If the previous value of the id_class (which is stored in @class) is not equal to the current value (which is stored in id_class), the @student is zeroed. Otherwise is is incremented.
  4. @class is assigned with the new value of id_class, and it will be used in test on step 3 at the next row.

相关文章