Как выполнить сгруппированный рейтинг в MySQL

Ответ 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

Это работает очень просто:

• Первоначальный запрос упорядочивается id_class во-первых, id_student второй.
• @student и @class инициализируются -1
• @class используется для проверки того, введен ли следующий набор. Если предыдущее значение id_class (которое хранится в @class) не равно текущему значению (которое хранится в id_class), то @student обнуляется. В противном случае увеличивается.
• @class присваивается новое значение id_class, и оно будет использоваться в тесте на шаге 3 в следующей строке.

Ответ 2

Существует проблема с решением Quassnoi (помечено как лучший ответ).

У меня такая же проблема (например, имитация функции SQL Window в MySQL), и я использовал для реализации Quassnoi решение, используя пользовательские переменные для хранения значения предыдущей строки...

Но, возможно, после обновления MySQL или что-то еще, мой запрос больше не работал. Это связано с тем, что порядок оценки полей в SELECT не гарантируется. Назначение @class может быть оценено до назначения @student, даже если оно помещено после в SELECT.

Это упоминается в документации MySQL следующим образом:

Как правило, вы никогда не должны присваивать значение пользовательской переменной и прочитайте значение в пределах одного и того же оператора. Вы можете получить ожидаемых результатов, но это не гарантируется. Получатель чего-то оценка выражений с использованием пользовательских переменных undefined и может изменяться на основе элементов, содержащихся в данном заявлении; кроме того, этот порядок не гарантируется одинаковым между релизы MySQL Server.

источник: 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), чтобы ожидаемый результат был прозрачным.

Не очень элегантный, но он работает!

Ответ 3

Модифицированный сверху, это работает, но его сложнее, чем я думаю, это должно быть:

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

Ответ 4

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

Ответ 5

Я сделал несколько поисков, нашел эту статью, чтобы придумать это решение:

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;

Любые мысли о том, что лучше?