Mysql多列行重复，删除语句

Question

所以我很难让这份声明正常运作。我有一个4列的表: ID、CertID、GroupID、CompID。随着时间的推移，数据库收集了一些重复的行，我指的是在所有列上重复的行(不包括作为其键的ID字段)。

我一直试图让SELECT语句在实际DELETE语句之前工作，这样我就不必恢复备份的DB了。

这就是我最近一直在尝试的：

SELECT u1.* FROM CertMain AS u1, CertMain AS u2 
WHERE u1.CertID = u2.CertID 
AND u1.GroupID = u2.GroupID 
AND u1.CompID = u2.CompID

但是这似乎并没有给我想要的东西，任何帮助都是非常感谢的。

----------------------------------
| ID | CertID | GroupID | CompID |
|----|--------|---------|--------|
| 1  |   1    |    4    |   1    | <---Duplicate Row
| 2  |   1    |    4    |   3    | <---NOT A Duplicate Row (All 3 must match)
| 3  |   1    |    4    |   1    | <---Duplicate Row
| 4  |   8    |    3    |   5    |
| 5  |  10    |    1    |   1    |
---------------------------------- 

Answer 1

用group by怎么样？

select CertID, GroupID, CompID, group_concat(id)
from CertMain
group by CertID, GroupID, CompID 
having count(*) > 1;

这就将所有的ids放在一行上。

如果您想要实际的行，那么使用exists

select cm.*
from CertMain cm
where exists (select 1
              from CertMain cm2
              where cm2.certid = cm.certid and cm2.groupid = cm.groupid and
                    cm2.compid = cm.compid and cm2.id <> cm.id
             );

编辑：

如果您想将它转换为delete in MySQL，那就麻烦多了。如果group by具有良好的性能，那么您可以：

delete cm
    from CertMain cm  join
         (select CertID, GroupID, CompID, min(id) as minid
          from CertMain
          group by CertID, GroupID, CompID 
         ) cm2
         on cm2.certid = cm.certid and cm2.groupid = cm.groupid and
            cm2.compid = cm.compid and cm.id > cm2.minid;

在执行删除操作之前，应将当前表保存在另一个表中。还请注意，如果这三个ids中的任何一个是NULL，则这将不完全按需要工作。

Answer 2

可以使用HAVING COUNT子句定位重复行，如下所示：

SELECT CertID, GroupID, CompID, count(*) as duplicated_rows
FROM CertMain
GROUP BY CertID, GroupID, CompID 
HAVING COUNT(*) > 1

Answer 3

从您目前的情况来看，您发现每一行都有自己的副本，其中包含u1.ID = u2.ID。你需要比较一下u1.ID > u2.ID

SELECT u1.* FROM CertMain AS u1, CertMain AS u2 
WHERE u1.CertID = u2.CertID 
AND u1.GroupID = u2.GroupID 
AND u1.CompID = u2.CompID
AND u1.ID > u2.ID