MySQL 5.0实现每个用户最多3条高分记录（同一评估仅保留最高分）的SQL查询需求及问题解决

先明确你的核心需求：

• 对每个用户的同一评估（assessment_id），仅保留分数最高的1条记录
• 从这些去重后的记录里，每个用户最多取3条分数最高的记录

你的原SQL问题分析 #

你的现有查询逻辑混乱，主要问题包括：

• 按id分组毫无意义（id是唯一主键，分组后每条记录都是单独一组）
• 窗口函数的分区字段错误，无法实现“用户+评估”的去重逻辑
• 外层额外分组进一步打乱了数据筛选规则

正确的SQL实现 #

我们可以用**两步CTE（公共表表达式）**来清晰实现需求：

WITH user_assessment_top_score AS (
    SELECT 
        id,
        modules_completion_status_id,
        attempt_score,
        user_id,
        assessment_id,
        -- 为每个用户+评估分组，按分数降序编号，取最高分的1条
        ROW_NUMBER() OVER (
            PARTITION BY user_id, assessment_id 
            ORDER BY attempt_score DESC, id DESC -- 同分情况下取id最大的记录（最新尝试）
        ) AS rn_assessment
    FROM assessment_attempt_score
),
user_top_3_scores AS (
    SELECT 
        id,
        modules_completion_status_id,
        attempt_score,
        user_id,
        assessment_id,
        -- 为每个用户分组，按分数降序编号，取前3条
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY attempt_score DESC, id DESC
        ) AS rn_user
    FROM user_assessment_top_score
    WHERE rn_assessment = 1 -- 仅保留每个用户每个评估的最高分记录
)
SELECT 
    id,
    modules_completion_status_id,
    attempt_score,
    user_id,
    assessment_id
FROM user_top_3_scores
WHERE rn_user <= 3 -- 每个用户最多取3条
ORDER BY attempt_score DESC, id DESC; -- 按分数从高到低输出

代码逻辑说明 #

1. 第一步CTE（user_assessment_top_score）：
   • 按user_id + assessment_id分区，确保我们处理的是同一个用户的同一个评估
   • 按attempt_score降序排序，给每个分区内的记录编号，编号为1的就是该用户该评估的最高分记录（同分的话取id最大的，也就是最新的尝试）
2. 第二步CTE（user_top_3_scores）：
   • 基于第一步的去重结果，按user_id分区
   • 再次按attempt_score降序排序，给每个用户的记录编号，取编号≤3的，也就是每个用户最多3条高分记录
3. 最终查询：
   • 筛选出每个用户的前3条记录，并按分数从高到低排序输出，和你期望的样本格式一致

验证样本数据 #

以用户1473为例：

• 他的assessment_id=1097最高分是99，assessment_id=700是93，assessment_id=684最高分是80，这三条会被选中，符合样本输出的前几条结果。

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