怎么在 MySQL 中进行分组排名

本教程将介绍如何在 MySQL 中进行分组排名的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

怎么在 MySQL 中进行分组排名 教程 第1张

问题描述

所以我有一张如下表:

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

现在我知道您可以使用临时变量进行排名,喜欢这里,但是我怎么为分组集做呢?感谢您的任何见解!

推荐答案

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 步的测试.

    Initial query is ordered by id_class first, id_student second.

    @student and @class are initialized to -1

    @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.

    @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 中进行分组排名的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。