Como executar uma sorting agrupada no MySQL

Então eu tenho uma tabela da seguinte forma:

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 

Eu preciso então agrupar, classificar e ordenar que eles forneçam:

 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 

Agora eu sei que você pode usar uma variável temporária para classificar, como aqui , mas como faço para um conjunto agrupado? Obrigado por qualquer 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 

Isso funciona de uma maneira muito simples:

  1. A consulta inicial é ordenada por id_class primeiro, id_student second.
  2. @student e @class são inicializados para -1
  3. @class é usado para testar se o próximo conjunto é inserido. Se o valor anterior da id_class (que é armazenado em @class ) não for igual ao valor atual (que é armazenado em id_class ), o @student será zerado. Caso contrário, é incrementado.
  4. @class é atribuído com o novo valor de id_class e será usado no teste na etapa 3 na próxima linha.

Existe um problema com a solução da Quassnoi (marcada como melhor resposta).

Eu tenho a mesma problemática (ou seja, simulando SQL Window Function no MySQL) e eu usei para implementar a solução Quassnoi, usando variables ​​definidas pelo usuário para armazenar o valor da linha anterior …

Mas, talvez depois de uma atualização do MySQL ou qualquer outra coisa, minha consulta não funcionou mais. Isso ocorre porque a ordem de avaliação dos campos no SELECT não é garantida. A designação @class pode ser avaliada antes da designação @student, mesmo que seja colocada após o SELECT.

Isso é mencionado na documentação do MySQL da seguinte forma:

Como regra geral, você nunca deve atribuir um valor a uma variável de usuário e ler o valor dentro da mesma instrução. Você pode obter os resultados esperados, mas isso não é garantido. A ordem de avaliação para expressões envolvendo variables ​​de usuário é indefinida e pode mudar com base nos elementos contidos em uma determinada instrução; Além disso, não é garantido que esta ordem seja a mesma entre as versões do servidor MySQL.

fonte: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Finalmente eu usei um truque como esse para ter certeza de atribuir @class AFTER lendo:

 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 

Usar a function left () é usado apenas para definir a variável @class. Em seguida, concatene o resultado de left () (igual a NULL) para o resultado esperado é transparente.

Não é muito elegante, mas funciona!

Modificado de cima, isso funciona, mas é mais complexo do que eu acho que precisa ser:

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

Resultado:

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

Eu fiz alguma pesquisa, encontrei este artigo para chegar a esta solução:

 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; 

Quaisquer pensamentos sobre qual é melhor?