mysql - 如何删除MySQL中具有一些相同值的行？

Question

我有一个大约有 11 个字段(列)的表，并且我知道有多个重复条目，但它们仅在少数字段内重复。我想要做的是，如果两行或更多行具有相同的小写 ProviderName 和 Address 以及相同的 StateID，那么我想删除那些额外的行，只留下一份副本。我有这样的东西，但我不知道如何完成它。

SET SQL_SAFE_UPDATEs=0;
DELETE FROM providers
WHERE LCASE(ProviderName), LCASE(Address), StateID;

更新:

SET SQL_SAFE_UPDATES=0;
DELETE p1.* 
FROM providers AS p1
JOIN providers AS p2 
ON LCASE(p1.ProviderName) = LCASE(p2.ProviderName)
AND LCASE(p1.Address) = LCASE(p2.Address)
AND p1.Zip = p2.Zip
AND p1.StateID = p2.StateID
AND p1.ProviderId > p2.ProviderId

Answer 1

此答案假设您有一个唯一的 id 字段，用于区分具有重复值的行。它将保留具有最低 id 的行。

DELETE p1.*
FROM providers AS p1
JOIN providers AS p2 
ON LCASE(p1.ProviderName) = LCASE(p2.ProviderName)
AND LCASE(p1.Address) = LCASE(p2.Address)
AND p1.StateID = p2.StateID
AND p1.id > p2.id

请注意，如果您的表使用不区分大小写的排序规则，则无需调用 LCASE。

更有效的查询可能是:

DELETE p1.*
FROM providers AS p1
JOIN (SELECT LCASE(ProviderName) AS Name, LCASE(Address) AS Addr, StateID, MIN(id) AS id
      FROM providers
      GROUP BY Name, Addr, StateID
      HAVING COUNT(*) > 1) AS p2
ON LCASE(p1.ProviderName) = p2.Name
AND LCASE(p1.Address) = p2.Addr
AND p1.StateID = p2.StateID
AND p1.id > p2.id

叉积要小得多，因为我们首先将所有具有相同值的提供商分组。此外，HAVING 子句会过滤掉所有非重复项。