如何为列表中每个player_id执行SQL查询获取最新score

表结构与数据 #

scores表（id为自增主键）的结构及数据如下：

需求说明 #

给定一组player_id（示例为3和4），返回每个player_id对应的最新得分记录（即该player_id下id最大的那条score），要求用单个查询完成，且避免使用UNION（因player_id数量可能多达数百个）。

可行方案 #

方案1：子查询取最大id后关联

先筛选目标玩家的最大id，再关联原表获取对应score：

SELECT s.score
FROM scores s
INNER JOIN (
    SELECT player_id, MAX(id) AS max_id
    FROM scores
    WHERE player_id IN (3, 4)  -- 替换为你的player_id列表
    GROUP BY player_id
) t ON s.id = t.max_id
ORDER BY t.player_id;  -- 可选：按player_id排序，保证结果顺序与输入列表对应

方案2：窗口函数筛选（适用于MySQL 8.0+、PostgreSQL等）

用ROW_NUMBER()窗口函数按player_id分组，按id倒序取每组第一条：

SELECT score
FROM (
    SELECT 
        score,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY id DESC) AS rn
    FROM scores
    WHERE player_id IN (3, 4)
) t
WHERE rn = 1
ORDER BY player_id;

方案3：自连接排除非最新记录

通过自连接过滤掉每个玩家id非最大的记录：

SELECT s1.score
FROM scores s1
LEFT JOIN scores s2 
    ON s1.player_id = s2.player_id 
    AND s1.id < s2.id
WHERE s1.player_id IN (3, 4)
AND s2.id IS NULL
ORDER BY s1.player_id;

以上三种方法均无需使用UNION，可批量处理任意数量的player_id，且查询效率稳定，适合数据量较大的场景。

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