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

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 * from 

(SELECT name, email,

COUNT(name) OVER (PARTITION BY name, email) cnt 

FROM users)

WHERE cnt >1;

其他回答

删除名称重复的记录

;WITH CTE AS    
(

    SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS T FROM     @YourTable    
)

DELETE FROM CTE WHERE T > 1
select name, email
, case 
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users

尝试此代码

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

试试看:

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

如果您与Oracle合作,最好采用以下方式:

create table my_users(id number, name varchar2(100), email varchar2(100));

insert into my_users values (1, 'John', 'asd@asd.com');
insert into my_users values (2, 'Sam', 'asd@asd.com');
insert into my_users values (3, 'Tom', 'asd@asd.com');
insert into my_users values (4, 'Bob', 'bob@asd.com');
insert into my_users values (5, 'Tom', 'asd@asd.com');

commit;

select *
  from my_users
 where rowid not in (select min(rowid) from my_users group by name, email);