問題描述

所以我有一個表格如下:

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.

這篇關于如何在 MySQL 中執行分組排名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！