php - 查询清理旧表或创建新表

Question

我有一个包含特定玩家信息的players 表和一个包含有关特定“跳跃”的统计信息的jumps 表，任何玩家都可能在登录到的游戏中执行过数据库。 jumps 表还包含相应的“player id”，它在 players 表中也可用。

表结构如下(只显示重要列):

玩家:

id | name | ip | authid | lastseen

跳转:

pid | type | distance | wpn

“跳跃”按类型、距离 和wpn 记录。每当达到更大的distanceper type and wpn 时，就会更新一行，这意味着每个 type 都有不同的条目，每个 pid 的每个 wpn 都有不同的条目。

问题

---

用于保存信息的插件过去是由 ip 而不是 authid 设置的，这导致两个表中的条目过多，其中不同的 ip 对应一个额外的id/pid。

目标

---

1. 从 players 表开始，我想为每个 authid 查找并删除不包含最大 lastseen 值的所有行对于那个特定的 authid。最后，每个 authid 应该只有一行。
2. 现在是困难的部分:我不能简单地先执行删除查询来完成 1。我可能需要最后执行该任务，因为我需要知道 id/pid 才能更新 jumps 表中的所有行具有从 1 保留的唯一 id。但是，等等!还有更多!还记得条目过去是如何由 ip 而不是 authid 创建的吗？现在可能会创建“重叠”行，因为每个 authid 可能有多个 id/pid 值。现在需要删除这些。

还在迷茫吗？让我在下面提供一个例子:

例子

---

这里我执行了一个简单的连接来匹配两个表中的相应行并提供一个示例:

SELECT * FROM uq_jumps INNER JOIN uq_players ON uq_jumps.pid = uq_players.id WHERE authid="STEAM_X:X:XXX"AND type="bj"

这导致以下内容我重新排序/格式化以提高可读性:

675 | bj | 205251568 | awp
675 | name1 | 0.0.0.0 | STEAM_X:X:XXX | 1395562981

2457 | bj | 244130768 | knife
2457 | name2 | 11.11.11.11 | STEAM_X:X:XXX | 1384822930

2566 | bj | 246756784 | usp
2566 | name3 | 10.10.10.10| STEAM_X:X:XXX | 1381841664

2693 | bj | 242543040 | knife
2693 | name3 | 9.9.9.9 | STEAM_X:X:XXX | 1381934217

3483 | bj | 235620544 | usp
3483 | name3 | 8.8.8.8 | STEAM_X:X:XXX | 1382803345

3701 | bj | 243075456 | usp
3701 | name2 | 7.7.7.7 | STEAM_X:X:XXX | 1384996069

3764 | bj | 244422656 | usp
3764 | name3 | 6.6.6.6 | STEAM_X:X:XXX | 1383253511

4070 | bj | 243392880 | knife
4070 | name3 | 5.5.5.5 | STEAM_X:X:XXX | 1383550208

4179 | bj | 240038976 | knife
4179 | name2 | 4.4.4.4 | STEAM_X:X:XXX | 1383642095

4260 | bj | 243244656 | usp
4260 | name2 | 3.3.3.3 | STEAM_X:X:XXX | 1383821359

5369 | bj | 242361200 | knife
5369 | name2 | 2.2.2.2 | STEAM_X:X:XXX | 1384629584

5905 | bj | 245641984 | usp
5905 | name2 | 1.1.1.1 | STEAM_X:X:XXX | 1385424952

675 | bj | 245942608 | usp
675 | name1 | 0.0.0.0 | STEAM_X:X:XXX | 1395562981

675 | bj | 212841424 | m249
675 | name1 | 0.0.0.0 | STEAM_X:X:XXX | 1395562981

更新和插入行的插件认为“knife”等同于“usp”，所以最后应该保留上面的唯一条目是:

675 | bj | 205251568 | awp
675 | name1 | 0.0.0.0 | STEAM_X:X:XXX | 1395562981

2566 | bj | 246756784 | usp
2566 | name3 | 10.10.10.10| STEAM_X:X:XXX | 1381841664

675 | bj | 212841424 | m249
675 | name1 | 0.0.0.0 | STEAM_X:X:XXX | 1395562981

---

关于如何生成唯一条目的新表或选择/删除应该正确删除或保留的条目，我在这里完全不知所措。我最初的想法是从使用类似我在上面发布的连接语句开始的。

现在我可以在 PHP 脚本中通过多个查询和遍历结果数组等轻松地做到这一点，但有人告诉我 2 可以在单个查询中完成(当然这人还没有证明他们的观点)。

任何建议、想法甚至起点都会有所帮助。

Answer 1

首先将players表结构复制到一个临时表中，假设是new_players

CREATE TABLE new_players SELECT * FROM players WHERE 1=2;

现在在新创建的表中插入所需的 authid 和 lastseen 字段

INSERT INTO new_players 
SELECT authid, MAX(lastseen) AS lastseen FROM players GROUP BY authid;

填写剩余字段

UPDATE new_players np JOIN players p 
  ON np.authid=p.authid AND np.lastseen=p.lastseen 
SET np.id   = p.id,
    np.name = p.name,
    np.ip   = p.ip
;

用正确的一个重新映射跳跃中的 pid(根据 new_players 中的选定记录)

UPDATE jumps j LEFT JOIN new_players np ON j.pid = np.id SET
j.pid = (SELECT np2.id FROM players p JOIN new_players np2 
           ON p.authid=np2.authid AND p.lastseen=np2.lastseen 
         WHERE p.id=j.pid LIMIT 1
        )
WHERE np.id IS NULL;

现在您可以安全地清除不需要的记录

DELETE p.* FROM players p LEFT JOIN new_players np ON p.id=np.id 
WHERE np.id IS NULL;

最后清除跳转表

DELETE j.* FROM jumps LEFT JOIN (
  SELECT pid, type, wpn, MAX(distance) AS distance FROM jumps GROUP BY pid, type, wpn
) j2 ON j.pid=j2.pid AND j.type=j2.type AND j.wpn=j2.wpn AND j.distance=j2.distance
WHERE j2.pid IS NULL;

注意:因为我无法测试代码，所以不确定语法有效性。但我希望你能理解主要思想。
注意:在测试新东西之前总是备份