How to Perform Grouped Ranking in MySQL?

Detailed explanation of MySQL group ranking

Database administrators often need to sort and rank grouped data. For example, a student performance table, containing class and student information as well as grades, needs to be grouped by class and rank student performance in each class.

MySQL can achieve group ranking through the following methods:

SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class = id_class THEN @student+1 ELSE 0 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1, @class := -1) AS init_vars,  -- 初始化变量
  (SELECT *
   FROM mytable
   ORDER BY id_class, grade DESC  -- 按班级和成绩降序排序
  ) AS t

This query works like this:

1. Variable initialization: Use subquery (SELECT @student:= -1, @class := -1) AS init_vars to initialize user-defined variables @student and @class to -1.

2. Grouped Iteration: The query selects data from a temporary table t that has been sorted by id_class and grade in descending order.

3. Rank Calculation: The CASE statement calculates the rank (rn) of each student based on the current id_class and the previous @class stored in id_class. If id_class changes, @student is reset to 0; otherwise, @student is incremented.

4. Update class variable : @class is updated to the current value of id_class for the next iteration.

The query results will be a list of students in each class, sorted by their grades in descending order, and sorted by their rank in the class. This method efficiently performs grouped ranking, allowing you to easily retrieve sorted and ranked data in complex scenarios involving grouped data. Note that the sorting basis here is grade descending order, and the sorting field and order can be adjusted according to actual needs.

The above is the detailed content of How to Perform Grouped Ranking in MySQL?. For more information, please follow other related articles on the PHP Chinese website!