MySQL排名问题：如何调整查询以获取正确的多维度排名结果

嘿，我太懂这种“数据都出来了但排名不对”的憋屈感了！结合你说的学校+评价表的场景，我猜你大概率是没把窗口函数的分区（PARTITION BY）和排序（ORDER BY）逻辑搞对——这可是多维度排名的核心。

先给你一套基于MySQL 8.0+的标准解决方案（现在主流环境基本都是8.0以上了，要是用5.x版本我后面补方案）：

先明确表结构示例（方便对应你的实际表） #

假设：

• schools表（学校数据）：school_id, country, region, school_name
• ratings表（评价数据）：rating_id, school_id, total_score（你的“评价总分”字段）

核心查询：多维度排名一次性搞定 #

SELECT
    s.school_id,
    s.school_name,
    s.country,
    s.region,
    -- 计算评价总分的平均值
    AVG(r.total_score) AS avg_total_score,
    -- 整体排名（所有学校按平均分降序）
    RANK() OVER (ORDER BY AVG(r.total_score) DESC) AS overall_rank,
    -- 国家内排名（同一国家内按平均分降序）
    RANK() OVER (PARTITION BY s.country ORDER BY AVG(r.total_score) DESC) AS country_rank,
    -- 地区内排名（同一地区内按平均分降序）
    RANK() OVER (PARTITION BY s.region ORDER BY AVG(r.total_score) DESC) AS region_rank
FROM
    schools s
JOIN
    ratings r ON s.school_id = r.school_id
GROUP BY
    s.school_id, s.school_name, s.country, s.region
ORDER BY
    overall_rank;

关键细节说明 #

• 窗口函数选择：

  • RANK()：遇到相同平均分的学校会并列排名，之后的排名会跳号（比如两个第1，下一个是第3）
  • DENSE_RANK()：相同分数并列，之后排名不跳号（两个第1，下一个是第2）
  • ROW_NUMBER()：哪怕分数相同，每个学校也会得到唯一的连续排名（适合不允许并列的场景）
    你可以根据需求把上面的RANK()换成对应的函数。

• 分区逻辑：PARTITION BY s.country就是告诉MySQL“在同一个国家的范围内单独计算排名”，地区同理；如果不加PARTITION，就是全局的整体排名。

如果你用的是MySQL 5.x（不支持窗口函数） #

那得用用户变量来模拟排名，举个国家内排名的例子：

SELECT
    school_id,
    school_name,
    country,
    avg_total_score,
    @current_rank := IF(@current_country = country, 
                        IF(@current_score = avg_total_score, @current_rank, @current_rank + 1),
                        1) AS country_rank,
    @current_country := country,
    @current_score := avg_total_score
FROM (
    SELECT
        s.school_id,
        s.school_name,
        s.country,
        AVG(r.total_score) AS avg_total_score
    FROM schools s
    JOIN ratings r ON s.school_id = r.school_id
    GROUP BY s.school_id, s.school_name, s.country
    ORDER BY country, avg_total_score DESC
) AS ranked_schools,
(SELECT @current_rank := 0, @current_country := '', @current_score := 0) AS rank_init;

这个逻辑是先按国家和平均分排序，然后用变量判断当前行和上一行是否同国家、同分数，来更新排名。

如果你的现有查询还是有问题，可以对照上面的逻辑检查：是不是分组时遗漏了必要字段？是不是排序方向搞反了（比如用了ASC导致低分排前面）？或者是不是分区字段选得不对（比如地区字段有大小写/格式不一致的情况）？

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