How to Perform Grouped Ranking Queries in MySQL?

MySQL Group Ranking Query: Complete Guide

In data processing, it is often necessary to determine the ranking of each record within a group. In MySQL, this functionality can be achieved using window functions such as ROW_NUMBER() or RANK() in conjunction with a grouping clause.

Consider the following form:

学生ID (ID_STUDENT)	班级ID (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

To create grouped rankings, you can use the following query:

SELECT id_student, id_class, grade,
   @student:=CASE 
       WHEN @class = id_class THEN @student + 1
       ELSE 1 
   END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= 0) s,
  (SELECT @class:= 0) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, grade DESC, id_student
  ) t;

How it works:

1. Sort rows: The subquery in the ORDER BY clause ensures that the data is sorted first by id_class, then by grade in descending order, and finally by id_student. This ensures that students with higher grades within the same class are ranked higher.

2. Variable initialization: Initialize two user-defined variables @student and @class to 0 using separate subqueries. These variables are used to track rank and class group respectively.

3. Grouping and Ranking: The main query uses a CASE expression to determine the ranking within each group. For rows in the same group (based on id_class), the rank is incremented; for a new group, the rank is reset to 1. The modified @student variable is assigned a ranking value.

4. Update class group: The @class variable is updated with the current id_class value to keep track of the active group.

5. Final result: The query selects the relevant columns, including the rank (rn) calculated using user-defined variables.

This grouped ranking query allows you to efficiently determine the ranking of students within various classes in MySQL. This method combines subqueries, user-defined variables, and conditional statements to achieve the desired results. Note that this method is slightly different from the original article. There are problems with the variable initialization and increment logic of the original article, which can easily lead to ranking errors. This method corrects this problem and ensures the accuracy of ranking results.

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