使用一个字段很容易找到重复项:

SELECT email, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

所以如果我们有一张桌子

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

这个查询将告诉我们John、Sam、Tom和Tom,因为他们都有相同的电子邮件。

然而,我想要的是获得相同电子邮件和名称的副本。

也就是说,我想得到“汤姆”,“汤姆”。

我需要这个的原因是:我犯了一个错误,允许插入重复的名称和电子邮件值。现在我需要删除/更改重复项,所以我需要先找到它们。


当前回答

SELECT name, email,COUNT(email) 
FROM users 
WHERE email IN (
    SELECT email 
    FROM users 
    GROUP BY email 
    HAVING COUNT(email) > 1)

其他回答

这是我想出的最简单的办法。它使用一个公共表表达式(CTE)和一个分区窗口(我认为这些特性在SQL 2008和更高版本中)。

此示例查找所有具有重复名称和dob的学生。要检查重复的字段位于OVER子句中。可以在投影中包含任何其他字段。

with cte (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

这里最重要的是要有最快的功能。还应确定副本索引。自连接是一个很好的选项,但为了获得更快的功能,最好先查找具有重复行的行,然后与原始表连接以查找重复行的id。最后按除id之外的任何列排序,以使重复的行彼此靠近。

SELECT u.*
FROM users AS u
JOIN (SELECT username, email
      FROM users
      GROUP BY username, email
      HAVING COUNT(*)>1) AS w
ON u.username=w.username AND u.email=w.email
ORDER BY u.email;

这将从每组重复项中选择/删除除一条记录之外的所有重复记录。因此,删除操作会留下所有唯一的记录+每组重复项中的一条记录。

选择重复项:

SELECT *
FROM <table>
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY <column1>, <column2>
);

删除重复项:

DELETE FROM <table>
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY <column1>, <column2>
);

请注意大量的记录,这可能会导致性能问题。

从表中的重复记录检查。

select * from users s 
where rowid < any 
(select rowid from users k where s.name = k.name and s.email = k.email);

or

select * from users s 
where rowid not in 
(select max(rowid) from users k where s.name = k.name and s.email = k.email);

删除表中的重复记录。

delete from users s 
where rowid < any 
(select rowid from users k where s.name = k.name and s.email = k.email);

or

delete from users s 
where rowid not in 
(select max(rowid) from users k where s.name = k.name and s.email = k.email);

如果要查找重复的数据(通过一个或多个标准),请选择实际的行。

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/