mysql - MySQL数据库中如何按时差去除重复项

Question

我已经查看了具有类似主题的其他问题，但它并没有解决我目前遇到的问题。

我有两个表:

users (id, name)

projects (id, user_id, image, inserted)

由于 Flash 应用程序中的错误，表“项目”中存在许多重复项(一个项目被多次添加)。连续副本之间有几秒的时间差(小于 10 秒)，这是确定重复的唯一方法(用户可以添加无限数量的项目，但创建一个至少需要一分钟)。

如何选择和删除副本(并保留原始副本)？

编辑:

Robin CaSTLin 在下面发布的解决方案几乎已经存在，但是这个查询:

SELECT p2.id
FROM project AS p
INNER JOIN project AS p2
ON p.id != p2.id AND p.user_id = p2.user_id AND 
    ABS(TIME_TO_SEC(TIMEDIFF(p.inserted, p2.inserted))) <= 10
GROUP BY p2.id

选择所有副本(如果用户添加项目 5 次，它会给我 5 个 ID)。那么让我们扭转问题:如何从该组中选择除第一个/最后一个以外的所有内容？还是只有第一个/最后一个？

Answer 1

CREATE TEMPORARY TABLE tmp_project (
    p1_id INT,
    p2_id INT
)
SELECT p.id, p2.id
FROM project AS p
INNER JOIN project AS p2
ON p.user_id = p2.user_id AND 
    ABS(TO_SECONDS(TIME_DIFF(p.inserted, p2.inserted))) <= 10;

SELECT p2_id
FROM tmp_project
WHERE p2_id NOT IN (SELECT p2_id
                    FROM tmp_project
                    GROUP BY p1_id)
GROUP BY p2_id;

我现在让它变得有点复杂。因为我们需要使用相同的行 2 次来过滤第一次出现的情况，所以我创建了一个临时表并在之后处理它。我加入所有案例，即使是在同一个 ID 上，然后使用 NOT IN 和 GROUP BY p1_id 过滤它。

如果重复的图像数据相同，也可以使用此解决方案:

在这些情况下，image 字段不应该相同吗？

SELECT id
FROM project
WHERE id NOT IN (   SELECT id
                    FROM projects
                    GROUP BY image, user_id)

这将使您得到一个列表，其中包含不是表中第一个的所有重复项。

---

然后获取这些 ID 并简单地

DELETE FROM project WHERE id IN (id1, id2, id3, ...)