您可以按照以下方式使用 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)
。