多时段数据集下排行榜SQL查询仅返回单条结果问题排查

核心问题分析 #

你的问题本质是SQL逻辑未正确限定数据集范围就进行分组/取最优记录。当数据库存在多时段数据时，原查询可能先对用户所有时段的测试记录取最高WPM，再过滤time=60的结果——这会导致只有那些最高WPM恰好来自time=60的用户才会被保留，最终只返回一条符合条件的记录；而当仅保留time=60数据时，所有用户的最优记录都属于该时段，自然能正常返回。

常见错误场景及修正 #

1. 错误的分组过滤顺序（先分组后过滤） #

原SQL可能类似这种逻辑：

SELECT t.*
FROM tests t
JOIN (
    SELECT userId, MAX(wpm) AS max_wpm
    FROM tests
    GROUP BY userId
) t_max ON t.userId = t_max.userId AND t.wpm = t_max.max_wpm
WHERE t.time = 60
ORDER BY t.wpm DESC, t.accuracy DESC, t.createdAt DESC;

这种写法先取每个用户所有时段的最高WPM，再匹配原表记录并过滤time=60——如果用户的最高WPM来自其他时段，这条记录会被WHERE条件剔除，最终只有极少数用户符合要求。

修正写法：先过滤time=60的数据集，再分组取最优记录：

SELECT t.*
FROM tests t
JOIN (
    SELECT userId, MAX(wpm) AS max_wpm
    FROM tests
    WHERE time = 60 -- 先限定目标时段
    GROUP BY userId
) t_max ON t.userId = t_max.userId AND t.wpm = t_max.max_wpm
-- 处理平局：WPM相同时，取accuracy更高、创建时间更新的记录
WHERE t.time = 60
ORDER BY t.wpm DESC, t.accuracy DESC, t.createdAt DESC;

2. 更可靠的窗口函数实现（推荐） #

用ROW_NUMBER()窗口函数可以更清晰地处理多维度排序的平局场景，避免JOIN带来的逻辑复杂：

SELECT id, userId, wpm, accuracy, time, createdAt
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY userId 
            ORDER BY wpm DESC, accuracy DESC, createdAt DESC
        ) AS rank_num
    FROM tests
    WHERE time = 60 -- 先锁定目标时段
) ranked_tests
WHERE rank_num = 1 -- 取每个用户的最优记录
ORDER BY wpm DESC, accuracy DESC, createdAt DESC;

这个逻辑的执行步骤：

1. 筛选所有time=60的测试记录
2. 按userId分区，每个分区内按WPM降序、accuracy降序、createdAt降序排序，给每条记录标记排名
3. 只保留每个分区的第一条记录（即该用户time=60的最优测试）
4. 最后按排行榜规则整体排序

GraphQL Resolver 适配 #

确保你的resolver执行的是上述修正后的SQL，以下是示例代码（根据你使用的ORM/数据库库调整）：

async function leaderboard(parent, args, context) {
  const { time } = args; // 传入目标时段60
  const result = await context.db.query(`
    SELECT id, userId, wpm, accuracy, time, createdAt
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY userId 
                ORDER BY wpm DESC, accuracy DESC, createdAt DESC
            ) AS rank_num
        FROM tests
        WHERE time = $1
    ) ranked_tests
    WHERE rank_num = 1
    ORDER BY wpm DESC, accuracy DESC, createdAt DESC;
  `, [time]);
  return result.rows;
}

总结 #

问题根源并非JOIN或ORDER BY的语法错误，而是数据筛选和分组的顺序逻辑错误——没有先限定time=60的数据集，导致后续分组取的是用户全时段的最优记录，而非目标时段的。通过先过滤目标时段数据，再在子集内进行分组/窗口函数排序，即可解决该异常。

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