MySQL 查找并显示所有重复项

Question

我正在寻找数据库中的重复记录 - 但也显示所有重复记录。我当前的查询:

SELECT 'duplicate' as dup,c.Id, c.CreatedDate, c.email, c.Lead_Grade__c, count(c.email) 
FROM contact as c
Inner Join (select c.Id, c.email FROM contact as c group by c.email having count(c.email) > 1) as dup
ON c.email = dup.email
WHERE Lead_Grade__c is null;

这可以为每个重复记录提供 1 行。我想要每条重复记录有 2(或更多)行。所以，如果记录 X 和记录 Y 都有相同的电子邮件，那么我想显示两者。

有什么想法吗？

谢谢!

Answer 1

这会将联系人加入自身，并且只显示电子邮件相同且有超过 1 条记录的记录(假设 ID 是唯一值)

此结果集只能是重复的。

SELECT A.* 
FROM contact A
INNER JOIN CONTACT B
 on A.email = B.email
 and a.id <> b.ID
ORDER BY A.Email