MySQL排行榜问题：每个用户返回多条“最优”结果

嘿，我完全懂你碰到的这个坑——当同一个用户在某个分类下有好几条积分一模一样的最优帖子时，常规的分组查询要么会重复输出同一个用户，要么干脆漏掉部分预期结果，对吧？我来给你分享两种靠谱的解决思路，适配不同版本的MySQL。

先明确下咱们的核心需求：每个分类（由activityUid+activityType联合标识）下，每个用户只显示一次，取他在该分类下的最高积分，同时按积分进行排名，积分相同的用户排名要符合业务逻辑（比如同排名或者唯一排名）。

假设咱们的表结构大概是这样：

• users表：id（用户ID）、username（用户名）等基础字段
• posts表：id（帖子ID）、user_id（关联用户ID）、activity_uid、activity_type（分类标识）、reward_points（奖励积分）等字段

方法一：用窗口函数（MySQL 8.0+ 推荐） #

这是最简洁高效的方式，MySQL 8.0及以上版本支持窗口函数，能轻松处理这种分组取最优+排名的场景：

WITH user_top_posts AS (
    SELECT 
        p.user_id,
        u.username,
        p.activity_uid,
        p.activity_type,
        p.reward_points,
        -- 按分类+用户分组，同组内按积分降序，积分相同则取最新帖子（用帖子ID降序）
        ROW_NUMBER() OVER (
            PARTITION BY p.activity_uid, p.activity_type, p.user_id 
            ORDER BY p.reward_points DESC, p.id DESC
        ) AS row_num
    FROM posts p
    JOIN users u ON p.user_id = u.id
)
SELECT 
    activity_uid,
    activity_type,
    user_id,
    username,
    reward_points,
    -- 按分类计算排名：相同积分用户同排名（用DENSE_RANK），如果要唯一排名换ROW_NUMBER
    DENSE_RANK() OVER (
        PARTITION BY activity_uid, activity_type 
        ORDER BY reward_points DESC
    ) AS ranking
FROM user_top_posts
WHERE row_num = 1  -- 只保留每个用户在该分类下的最优帖子
ORDER BY activity_uid, activity_type, ranking;

关键说明： #

• ROW_NUMBER()用来给每个用户在分类下的帖子排序，row_num=1就锁定了该用户的最优记录（积分最高，积分相同则取最新的）
• DENSE_RANK()是排行榜常用的排名方式：比如两个用户都是100分，都会显示为第1名，下一个90分的是第2名；如果你的业务要求每个排名唯一，把DENSE_RANK()换成ROW_NUMBER()即可；如果要跳过排名（比如两个100分是第1、第1，下一个是第3），用RANK()

方法二：兼容MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本低于8.0，用子查询来实现同样的逻辑：

SELECT 
    p.activity_uid,
    p.activity_type,
    p.user_id,
    u.username,
    p.reward_points,
    -- 计算同分类下的排名：统计比当前积分高的不同积分数量+1，实现同积分同排名
    (SELECT COUNT(DISTINCT reward_points) 
     FROM posts 
     WHERE activity_uid = p.activity_uid 
       AND activity_type = p.activity_type 
       AND reward_points > p.reward_points) + 1 AS ranking
FROM posts p
JOIN users u ON p.user_id = u.id
-- 第一步：筛选出每个用户在分类下的最高积分帖子
WHERE (p.activity_uid, p.activity_type, p.user_id, p.reward_points) IN (
    SELECT 
        activity_uid,
        activity_type,
        user_id,
        MAX(reward_points) AS max_points
    FROM posts
    GROUP BY activity_uid, activity_type, user_id
)
-- 第二步：处理同一用户同一分类下多条积分相同的最优帖子，只保留一条（这里取最新的帖子ID）
AND p.id = (
    SELECT MAX(id) 
    FROM posts 
    WHERE activity_uid = p.activity_uid 
      AND activity_type = p.activity_type 
      AND user_id = p.user_id 
      AND reward_points = p.reward_points
)
ORDER BY p.activity_uid, p.activity_type, ranking;

额外优化建议 #

• 索引优化：如果帖子表数据量很大，一定要给activity_uid、activity_type、user_id、reward_points建立联合索引，比如：

  CREATE INDEX idx_posts_activity_user_points ON posts(activity_uid, activity_type, user_id, reward_points DESC);
  

  这能大幅提升分组和子查询的效率。
• 业务灵活调整：如果你的业务允许同一用户在分类下显示多条相同积分的帖子，只需要去掉row_num=1或者p.id = (...)的筛选条件即可，但这种情况在排行榜里比较少见。

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