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

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,因为他们都有相同的电子邮件。

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

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

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


当前回答

试试看:

                DECLARE @myTable TABLE
                (
                    id INT,
                    name VARCHAR(10),
                    email VARCHAR(50)
                );

                INSERT @myTable
                VALUES
                (1, 'John', 'John-email');
                INSERT @myTable
                VALUES
                (2, 'John', 'John-email');
                INSERT @myTable
                VALUES
                (3, 'fred', 'John-email');
                INSERT @myTable
                VALUES
                (4, 'fred', 'fred-email');
                INSERT @myTable
                VALUES
                (5, 'sam', 'sam-email');
                INSERT @myTable
                VALUES
                (6, 'sam', 'sam-email');


                WITH cte
                AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rowNum,
                           *
                    FROM @myTable)
                SELECT c1.id,
                       c1.name,
                       c1.email
                FROM cte AS c1
                WHERE 1 <
                (
                    SELECT COUNT(c2.rowNum)
                    FROM cte AS c2
                    WHERE c1.name = c2.name
                          AND c1.email = c2.email
                );

其他回答

从表中的重复记录检查。

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);

SELECT id,COUNT(id)FROM table1 GROUP BY id HAVING COUNT;

我认为这可以正确地搜索特定列中的重复值。

 select emp.ename, emp.empno, dept.loc 
          from emp
 inner join dept 
          on dept.deptno=emp.deptno
 inner join
    (select ename, count(*) from
    emp
    group by ename, deptno
    having count(*) > 1)
 t on emp.ename=t.ename order by emp.ename
/
select id,name,COUNT(*) from user group by Id,Name having COUNT(*)>1

如果要删除重复项,这里有一种比在三个子选择中查找偶数/奇数行更简单的方法:

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

因此,删除:

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

更容易阅读和理解IMHO

注意:唯一的问题是您必须执行请求,直到没有删除行,因为每次只删除每个重复项中的一行