如何查询指定游戏与月份的玩家名人堂？SQL语句优化求助

针对你需要生成等级优先、同等级取最高分的玩家名人堂，同时支持筛选游戏和月份的需求，我来帮你优化现有SQL语句，先分析问题再给出解决方案：

现有SQL的核心问题 #

1. 缺少筛选条件：没有对游戏类型（game）和时间范围（月份）做限制，导致返回所有游戏、所有时间的数据
2. 未处理同等级的最高分：只确保了取用户的最高等级，但同一等级下的多条记录会全部保留，无法筛选出该等级的最高分记录

优化后的SQL方案 #

方案1：兼容低版本MySQL（无窗口函数）

这种写法适合MySQL 5.x等不支持窗口函数的版本，通过NOT EXISTS子查询确保每条记录是用户在指定游戏和月份下的最优记录：

SELECT d1.user_id, d1.level, d1.score
FROM docs d1
-- 筛选目标游戏和月份（可根据需求修改参数）
WHERE d1.game = 'pacman' 
  AND YEAR(d1.data) = 2018 
  AND MONTH(d1.data) = 3
-- 确保当前记录是该用户的最优记录：无更高等级，或同等级下无更高分
AND NOT EXISTS (
    SELECT 1 
    FROM docs d2
    WHERE d2.user_id = d1.user_id
      AND d2.game = d1.game
      AND YEAR(d2.data) = YEAR(d1.data)
      AND MONTH(d2.data) = MONTH(d1.data)
      AND (d2.level > d1.level 
           OR (d2.level = d1.level AND d2.score > d1.score))
)
-- 最终按等级降序、分数降序排序
ORDER BY d1.level DESC, d1.score DESC;

方案2：MySQL 8.0+推荐（窗口函数写法）

窗口函数语法更简洁直观，适合高版本MySQL：

SELECT user_id, level, score
FROM (
    SELECT 
        user_id, 
        level, 
        score,
        -- 按用户分组，先按等级降序、再按分数降序排名
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY level DESC, score DESC
        ) AS rn
    FROM docs
    -- 筛选目标游戏和月份
    WHERE game = 'pacman'
      AND YEAR(data) = 2018
      AND MONTH(data) = 3
) AS ranked
-- 取每个用户的第1名（最优记录）
WHERE rn = 1
ORDER BY level DESC, score DESC;

效果验证 #

以你提供的3月pacman游戏测试数据为例，执行上述SQL会得到预期结果：

user_id  level  score
2        7      5500
1        7      5100

使用说明 #

• 你可以修改game、YEAR(data)、MONTH(data)的值来筛选不同游戏和月份的数据
• 如果需要支持动态时间范围（比如最近30天），可以将时间条件改为data >= DATE_SUB(NOW(), INTERVAL 30 DAY)

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