MySQL 5.6使用用户变量实现分组排名时结果异常的解决问询

Hey there! Let's get that grouped ranking working correctly. The issues in your original queries stem from inconsistent variable handling and edge-case logic gaps. Here's a reliable solution, plus a breakdown of what went wrong.

Correct Query Implementation #

This query properly handles grouping by course, ranks by vote descending, and avoids NULL values in the rank column:

SELECT 
    (@id := @id + 1) AS ID,
    t1.course AS COURSE,
    t1.course_desc AS COURSE_DESC,
    t1.name AS NAME,
    t1.vote AS VOTE,
    -- Calculate rank with clear, sequential logic
    CASE
        WHEN @prev_course != t1.course THEN
            @rank_count := 1
        WHEN @prev_vote != t1.vote THEN
            @rank_count := @rank_count + 1
        ELSE
            @rank_count
    END AS RANK,
    -- Update tracking variables after rank calculation
    @prev_course := t1.course,
    @prev_vote := t1.vote
FROM 
    -- Initialize all variables in one clean subquery
    (SELECT @id := 0, @prev_course := NULL, @prev_vote := NULL, @rank_count := 0) AS vars,
    -- Pre-sort the student data to ensure consistent order before variable logic runs
    (SELECT course, course_desc, name, vote FROM student ORDER BY course, vote DESC) AS t1
ORDER BY 
    t1.course, t1.vote DESC;

Key Improvements Over Your Original Queries #

• Pre-sorted Subquery: We first sort the student data in a subquery (t1). MySQL 5.6 doesn't guarantee that the main query's ORDER BY runs before variable calculations, so pre-sorting ensures the rows are processed in the correct order for ranking.
• Simplified Case Logic: The ranking logic is broken into clear, non-nested conditions:
  • Reset rank to 1 when we switch to a new course
  • Increment rank only when the vote changes (same course)
  • Keep the same rank when votes are tied
• Variable Update Order: We update @prev_course and @prev_vote after calculating the rank. This ensures each row's rank uses the previous row's values, not the current row's updated variables.

Why Your Original Queries Failed #

1. First Query (NULL Rank for Teresa):
   Your nested CASE statement used short-circuit evaluation that didn't account for the final row of a group. When Teresa's row (vote 0) came after Federico's (vote 6), the inner CASE didn't trigger a valid assignment, leaving the rank as NULL. The simplified logic above covers all edge cases, including the last row of any group.

2. Second Query (Broken Ranking):
   You switched to using course_desc for grouping checks but kept sorting by course. This mismatch meant the variable tracking didn't align with the row order, leading to completely incorrect ranking across all groups.

Expected Output #

Running the corrected query will give you a valid rank for every row, including Teresa in the Science course (rank 4):

ID COURSE COURSE_DESC NAME     VOTE RANK
1  100    Math        Mario    10   1
2  100    Math        Giovanna 8    2
3  100    Math        Federico 8    2
4  100    Math        Arianna  5    3
5  200    History     Teresa   10   1
6  200    History     Mario    9    2
7  200    History     Giovanna 7    3
8  200    History     Patrizio 3    4
9  300    Literacy    Arianna  10   1
10 300    Literacy    Giovanna 7    2
11 300    Literacy    Federico 6    3
12 400    Science     Giovanni 9    1
13 400    Science     Maria    9    1
14 400    Science     Giovanna 7    2
15 400    Science     Carlo    7    2
16 400    Science     Federico 6    3
17 400    Science     Teresa   0    4
18 500    Philosophy  Maria    10   1

内容的提问来源于stack exchange，提问作者user9846973