MySql 查询根据某些列字段从表中删除重复行？

Question

我的表名称是虚拟的，它包含 6 列，其中 ID、NAME、TIMESTAMP 和其他 3 列。

我想删除 ID、NAME 和 TIMESTAMP 值与其他行匹配的重复行。如果有多行具有相同的 ID、NAME 和 TIMESTAMP 值，则保留一行并删除其他行。

Answer 1

解决办法在这里

https://stackoverflow.com/a/5016434

我刚刚遇到的另一个可能的解决方案:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

如果您想保留具有最低 id 值的行或

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

https://stackoverflow.com/a/5770309

DELETE DupRows.*
FROM MyTable AS DupRows
   INNER JOIN (
      SELECT MIN(ID) AS minId, col1, col2
      FROM MyTable
      GROUP BY col1, col2
      HAVING COUNT(*) > 1
   ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2
      AND SaveRows.minId <> DupRows.ID;