如何获取管理员未完成高分提交的游戏列表及SQL实现

场景说明 #

这是一个假设场景：

• 存在游戏列表
• 用户可针对每个游戏提交分数
• Admin必须为每个用户提交更高分数的记录（无需考虑游戏整体最高分，只需覆盖所有用户）

以Game 1的分数列表为例：

id  game_id     user_id     opponent_id     score
1   1           1           2               1003
2   1           2           null            1002
3   1           3           null            1001

其中opponent_id为NULL的提交是Admin需要超越的，且无需重复针对同一用户提交，只需覆盖所有用户。上述示例中，Admin仅针对opponent_id = 2提交了分数，还需补充opponent_id = 3的提交。

需求与问题 #

需要获取所有Admin未针对特定用户提交更高分的游戏列表，即存在用户提交了分数，但Admin未提交对应更高分的游戏。具体步骤：

1. 获取每个用户opponent_id = NULL的最高分
2. 校验Admin是否针对这些用户提交了更高分数（此步骤存在实现难点）

期望输出为所有存在待Admin提交高分的游戏列表，类比国际象棋场景：每个游戏对应一局棋，Admin可与多名玩家对弈，需找出处于Admin回合的游戏。

疑问解答与代码实现 #

现有数据库结构支持性 #

现有数据库结构完全支持实现该需求，通过嵌套查询和NOT EXISTS校验即可完成逻辑判断。

性能优化建议 #

为提升查询性能，建议在scores表上创建以下联合索引：

• (opponent_id, game_id, user_id, score)：加速用户最高分的分组查询
• (game_id, opponent_id, score)：加速Admin提交记录的校验查询

参考SQL代码 #

SELECT *
FROM games
WHERE id IN (
    SELECT game_id
    FROM (
        -- 获取每个游戏中，每个用户opponent_id为NULL的最高分
        SELECT game_id, user_id, MAX(score) AS max_score
        FROM scores
        WHERE opponent_id IS NULL
        GROUP BY game_id, user_id
    ) g
    -- 筛选Admin未针对该用户提交更高分数的记录
    WHERE NOT EXISTS (
        SELECT 1
        FROM scores
        WHERE game_id = g.game_id
          AND opponent_id = g.user_id
          AND score > g.max_score
    )
)

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