对列表中的每个ID执行相同查询

Question

我有一个名为“scores”的表，其中id是自动递增的主键。

| id | player_id | score | | --- | --- | --- |
| 1 | 1 | 100 |
| 2 | 1 | 57 |
| 3 | 2 | 434 |
| 4 | 3 | 11 |
| 5 | 3 | 132 |
| 6 | 4 | 32 |
| 7 | 4 | 100 |
| 8 | 4 | 21 |

根据上述数据，对于给定的player_id列表，我需要返回每个player_id的最新得分（即具有该player_id的最大id对应的得分）。

例如，根据上述数据，player 1 应返回57分，player 2应返回434分，player 3应返回132分，而player 4应返回21分。

假设我得到了一个包含player_ids 3和4的列表，我想创建一个单个查询，返回第一行132分，第二行21分。

单独的查询语句如下：

SELECT score FROM scores WHERE player_id = 3 ORDER BY id DESC LIMIT 1;
SELECT score FROM scores WHERE player_id = 4 ORDER BY id DESC LIMIT 1;

我遇到困难的是如何将这两个查询合并成一个。由于列表可能包含数百个player_ids，因此我想避免使用UNION操作。

Bill Karwin · Answer

您可以使用NOT EXISTS子句如下：

SELECT t.id, t.player_id, t.score
FROM scores t
WHERE NOT EXISTS (
  SELECT 1 FROM scores tt
  WHERE tt.player_id = t.player_id AND tt.id > t.id
);

另一种选项是使用ROW_NUMBER窗口函数，如下所示：

SELECT t.id, t.player_id, t.score
FROM (
  SELECT t.id, t.player_id, t.score,
         ROW_NUMBER() OVER (PARTITION BY t.player_id ORDER BY t.id DESC) as rn
  FROM scores t
) t
WHERE rn = 1;

注意：您可以在WHERE子句中添加一个额外条件，仅包含所需的player_id，例如player_id IN (3,4)。

Martin York · Answer

你可以通过多种方法获得期望的结果。

方法1

SELECT *
FROM Scores
WHERE id IN (
        SELECT MAX(id)
        FROM scores
        GROUP BY player_id
        )

在这个方法中，子查询SELECT MAX(id) FROM scores GROUP BY player_id为每个player_id获取最大的/id/。然后基于这些id获取结果。

方法2

SELECT s.player_id, s.id, s.score
FROM scores s
JOIN (
    SELECT player_id, MAX(id) AS max_id
    FROM scores
    GROUP BY player_id
    ) t ON s.player_id = t.player_id AND s.id = t.max_id;

在这个方法中，子查询与上面的方法相似，但它还额外返回了player_id，用于连接以检索相应的数据。