php - 更新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

任何人都可以帮助我在 mysql 中实现这一目标。

Answer 1

下面的查询将按 ID_CLASS 和 GRADE 列的降序对结果进行排序。

查询

select `ID_STUDENT`, `ID_CLASS`, `GRADE`, ( 
    case `ID_CLASS`
    when @curA then @curRow := @curRow + 1 
    else @curRow := 1 and @curA := `ID_CLASS` end
) as `rank`
from `your_table_name` g,
(select @curRow := 0, @curA := '') r
order by `ID_CLASS`, `GRADE` desc;