mysql - 如何查找MySQL中重复的记录?

  显示原文与译文双语对照的内容

我想在MySQL数据库中取出重复的记录。 这可以通过以下方式完成:


SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt> 1

结果是:


100 MAIN ST 2

我想把它拉出来,以便它显示每一行是重复的。 就像这样:


JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST

关于如何做到这一点的任何想法? 我试图避免做第一个,然后用代码中的第二个查询查找重复项。

时间:

关键是重写这个查询,以便它可以用作子查询。


SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id)> 1) dup ON list.address = dup.address

为什么不只使用内部联接表?


SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.id

如果地址可能存在两次,则需要一个 DISTINCT 。

使用这里查询的电子邮件地址 Find查找重复的用户。。


SELECT users.name, users.uid, users.mail, from_unixtime(created)
FROM users
INNER JOIN (
 SELECT mail
 FROM users
 GROUP BY mail
 HAVING count(mail)> 1
) dupes ON users.mail = dupes.mail
ORDER BY users.mail;

另一个解决方案是使用表别名,例如:


SELECT p1.id, p2.id, p1.address
FROM list AS p1, list AS p2
WHERE p1.address = p2.address
AND p1.id!= p2.id

你真的在这种情况下采取原始表列表,创建两个 p retend表 -- p 1 和 p 2 --出来,然后上执行一个连接地址列( 第 3行) 。 4th行确保相同的记录不出现多次的结果("复制副本") 。

查找重复的地址要比看起来更复杂,尤其是当你需要精确的时候。 在这种情况下,MySQL查询是不够的。。

我在 SmartyStreets 工作,在那里我们做了地址验证和de-duplication等方面的工作,我也看到了许多类似的问题。

有几个第三方服务将标记列表中的重复项。 只使用一个MySQL子查询就不会考虑地址格式和标准的差异。 ( 对于我们的地址) 有一些制定这些标准的准则,但是只有少数供应商被认证执行这种操作。

所以,我建议你最好的答案是将表格导出到一个CSV文件,并将它提交到一个有能力的列表处理器。 其中一个是 LiveAddress,它将在几秒钟后自动完成。 它将用一个名为"重复"的新字段标记重复的行,并在其中标记一个 Y 值。

这将在一个表格中选择重复,没有子查询。


SELECT *
FROM (
 SELECT ao.*, (@r := @r + 1) AS rn
 FROM (
 SELECT @_address := 'N'
 ) vars,
 (
 SELECT *
 FROM
 list a
 ORDER BY
 address, id
 ) ao
 WHERE CASE WHEN @_address <> address THEN @r := 0 ELSE 0 END IS NOT NULL
 AND (@_address := address ) IS NOT NULL
 ) aoo
WHERE rn> 1

这个查询actially模拟 OracleSQL ServerROW_NUMBER() 礼物

有关详细信息,请参阅我博客中的文章:

我们可以找到重复项,具体取决于一个字段 also.For,这些情况你可以使用以下格式。


SELECT COUNT(*), column1, column2 
FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

不会是非常高效的,但它应该是有效的:


SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
 FROM list AS inner
 WHERE inner.address = outer.address)> 1;

...