MySQL 仅使用另一个表的最高值更新表

Question

对于一个游戏网站。
如果玩家的分数大于他的旧分数，则所有游戏都会被记录

所有玩家表(超过 10,000 名玩家)

CREATE TABLE games (
    PlayerID INT UNSIGNED,
    Date TIMESTAMP(12),
    Score BIGINT UNSIGNED DEFAULT 0,
    #...other data
);

我每月更新一次记录表 best。在我删除所有游戏之后。

最佳玩家表(前 50 名)

CREATE TABLE best (
    #...same as games, without final other data
    PlayerID INT UNSIGNED,
    Date TIMESTAMP(12),
    Score BIGINT UNSIGNED DEFAULT 0
);

所以我将 games 表中的 50 名最佳玩家添加到表 best 中:

INSERT INTO best (PlayerID, Date, Score)
   SELECT PlayerID, Date, Score FROM games ORDER BY Score DESC LIMIT 50;

之后(这就是我遇到问题的地方)，我尝试只在 best 中保留最好的 50 行。此时 best 包含 100 行。

我必须做的:

• 不要多次存储同一个玩家 PlayerID。
• 删除该玩家的最差分数。
• 最后，只留下前 50 名。

->

+----------+---------+
| PlayerID | Score   |
+----------+---------+
| 25       | 20000   | New
| 25       | 25000   | Old best
| 40       | 10000   | Old best
| 57       | 80000   | New best
| 57       | 45000   | Old
| 80       | 35000   | New best
+----------+---------+

最后我只需要保留 50 行(在我的示例中带有“最佳”的行)。
我尝试了很多东西，但没有成功达到预期的结果。

我正在使用 PHP，所以如果可以简单地使用数组中的中间存储来完成，那也很好。速度不是优先考虑的，因为它是每月只进行一次的操作。

Answer 1

以下 SQL 返回前 50 个分数:

SELECT `PlayerId`, max(`Score`) MaxScore
FROM (
    SELECT `PlayerId`, `Date`, `Score` FROM games    
    UNION
    SELECT `PlayerId`, `Date`, `Score` FROM best    
) t
GROUP BY `PlayerId`
ORDER BY `MaxScore` DESC
LIMIT 50

您可以使用结果覆盖表best。为此，您还需要相应的 Date 字段，目前缺少该字段。下一个 SQL 也会返回一个 maxDate 字段，它对应于最高分。

SELECT t2.`PlayerId`, max(t2.`Date`) maxDate, top.`MaxScore`  
FROM
    (   
        SELECT `PlayerId`, max(`Score`) MaxScore
        FROM (
            SELECT `PlayerId`, `Date`, `Score` FROM games    
            UNION
            SELECT `PlayerId`, `Date`, `Score` FROM best    
        ) t1
        GROUP BY `PlayerId`
        ORDER BY `MaxScore` DESC
        LIMIT 50
    ) top
    LEFT JOIN (
        SELECT `PlayerId`, `Date`, `Score` FROM games    
        UNION
        SELECT `PlayerId`, `Date`, `Score` FROM best    
    ) t2 ON t2.`PlayerId` = top.`PlayerId` AND t2.`Score` = top.`MaxScore`
GROUP BY t2.`PlayerId`
ORDER BY top.`MaxScore` DESC

要将新的前 50 名高分转移到 best 表中，您可以使用像 tmp_best 这样的临时表。将最高分数插入空表 tmp_best 中(您必须从上面插入您的选择查询):

INSERT INTO tmp_best (`PlayerId`, `Date`, `Score`) 
    SELECT ...

在此之后，可以清空 best 表，然后您可以将 tmp_best 中的行复制到 best 中。

---

这是一个替代解决方案，它简化了 SQL。区别上面的解决方案是在统一数据的开头使用临时表tmp_all。在使用以下 SQL 之前，您必须创建 tmp_all，它可以是 games 或 best 结构的副本。

DELETE FROM tmp_all;

INSERT INTO tmp_all
    SELECT `PlayerId`, `Date`, `Score` FROM games    
    UNION
    SELECT `PlayerId`, `Date`, `Score` FROM best    
;

DELETE FROM best;

INSERT INTO best (`PlayerId`, `Date`, `Score`) 
    SELECT t2.`PlayerId`, max(t2.`Date`) maxDate, top.`MaxScore`  
    FROM
        (   
            SELECT `PlayerId`, max(`Score`) MaxScore
            FROM tmp_all t1
            GROUP BY `PlayerId`
            ORDER BY `MaxScore` DESC
            LIMIT 50
        ) top
        LEFT JOIN tmp_all t2 ON t2.`PlayerId` = top.`PlayerId` AND t2.`Score` = top.`MaxScore`
    GROUP BY t2.`PlayerId`
    ORDER BY top.`MaxScore` DESC
;