mysql - 仅保留前 5 个高分的 SQL 命令

Question

我有一个 mySQL 数据库，例如如下表，

world    level    player_id     score
-----------------------------------------
1        1        1             100
1        1        2             123
1        1        3             130
1        1        4             200
1        1        5             90

1        2        8             234
.
.
.

对于每个唯一的(world, level, player_id) 三元组，我只想在新分数出现时记录前五个分数。

我的想法是执行以下操作:首先插入新的分数记录，例如

REPLACE INTO Highscores (world, level, player_id, score) VALUES (1, 1, 6, 500)

然后仅保留具有前 5 名分数的相同(世界，级别) 的记录，例如

DELETE FROM Highscores WHERE world=1 AND level=1 AND score < (SELECT min(score) FROM (SELECT score FROM Highscores ORDER BY score DESC LIMIT 5) AS Highscores);

但我想知道是否有其他方法可以做到这一点，也许使用一行 SQL，这可能会更有效？

平局得分:

我假设表中的最后一条记录是最后添加的，因此在平局的情况下，我想保留最后一条记录并删除较早的记录。

world    level    player_id     score
-----------------------------------------
1        1        1             100
1        1        2             200
1        1        3             100
1        1        4             100
1        1        5             100

1        1        8             200

这里，例如带有 player_id=8 的行将被保留，但带有 player_id=2 的行将被删除。 player_id=1, 3, 4, 5 也会保留。

更新

最后，通过引入 AUTO_INCRMENT 唯一的 tableid 作为主键，我选择了以下方法:

REPLACE INTO Highscores (world, level, player_id, score) VALUES (1, 1, 6, 500)

DELETE FROM Highscores
WHERE world=1 AND level=1 AND tableid NOT IN
(SELECT tableid FROM (SELECT tableid FROM Highscores WHERE world=1 AND level=1
       AND score >=
       (SELECT min(score) FROM
           (SELECT score FROM Highscores WHERE world=1 AND level=1 ORDER BY score DESC LIMIT 5)
           AS d)) AS c)

Answer 1

这可能不是您正在寻找的，但它应该有效。如果您使用 5 个“虚拟”值创建数据库，例如负的player_id 和 0 分数，则此查询将选择最低的值，并将其替换为新分数:

UPDATE Highscores
INNER JOIN 
(SELECT player_id
FROM Highscores
ORDER BY score ASC, player_id ASC LIMIT 1) rpid
ON Highscores.player_id = rpid.player_id
SET Highscores.player_id = 6, Highscores.score = 300

只需将最后一行替换为您需要的值即可。这是fiddle .

请注意，此解决方案基于这样的假设:player_id 始终设置为递增，并且仅使用一次。