如何删除“重复”记录？

Question

我有一个包含一些损坏记录的表，因为我忘记为两个列添加一个UNIQUE索引。请看下表中的示例：

+----+-------------+--------+------------+
| id | uuid        | object | project_id |
+----+-------------+--------+------------+
| 1  | 73621000001 | screw  | 1          |
| 2  | 73621000002 | screw  | 1          |
| 3  | 73621000003 | screw  | 1          |
| 4  | 73621000004 | tube   | 1          |
| 5  | 73621000005 | plate  | 2          |
| 6  | 73621000006 | plate  | 2          |
| 7  | 73621000007 | plate  | 2          |
| 8  | 73621000008 | plate  | 2          |
| 9  | 73621000009 | plate  | 2          |
| 10 | 73621000010 | gear   | 4          |
| 11 | 73621000011 | gear   | 4          |
+----+-------------+--------+------------+

如您所见，有一些object-project_id-combinations多次发生，但有不同的uuids。我想删除所有重复的记录，但保留那些最高的uuid。由此产生的表格应如下：

+----+-------------+--------+------------+
| id | uuid        | object | project_id |
+----+-------------+--------+------------+
| 3  | 73621000003 | screw  | 1          |
| 4  | 73621000004 | tube   | 1          |
| 9  | 73621000009 | plate  | 2          |
| 11 | 73621000011 | gear   | 4          |
+----+-------------+--------+------------+

我可以使用以下查询查看哪些object具有重复项：

SELECT uuid, object, project_id, COUNT(*)
FROM uuid_object_mapping
GROUP BY object, project_id
HAVING COUNT(*) > 1;

我可以使用以下查询获得“干净”表：

SELECT MAX(uuid) as uuid, object, project_id
FROM uuid_object_mapping
GROUP BY object, project_id;

并且我可以验证“干净”表没有包含重复的

SELECT uuid, object, project_id, COUNT(*)
FROM (
    SELECT MAX(uuid) as uuid, object_name, project_id
    FROM uuid_object_mapping
    GROUP BY object_name, project_id
) AS clean
GROUP BY object_name, project_id
HAVING COUNT(*) > 1;

但是，我如何删除所有不在“干净”表中的内容？

Answer 1

在MySQL中，您可以使用join，但是您需要注意NULL值：

delete om
    from uuid_object_mapping om join
         (select MAX(uuid) as uuid, object, project_id
          from uuid_object_mapping 
          group by object, project_id
         ) omkeep
         on omkeep.object = om.object and
            omkeep.project_id <=> om.project_id
    where om.uuid <> omkeep.uuid;

NULL值似乎已经消失，因此可以使用以下on子句：

         on omkeep.object = om.object and
            omkeep.project_id = om.project_id

Answer 2

请使用分区和顺序uuid子句。通过搜索分析。这是去除重复的最佳方法。

Answer 3

尝尝这个

select t1.id,t1.uuid,t1.object,t1.project_id from table as t1 inner join
(
select object,max(id) as id from table
group by object
) as t2 on t1.object=t2.object and t1.id=t2.id