使用一个字段很容易找到重复项：
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，因为他们都有相同的电子邮件。
然而，我想要的是获得相同电子邮件和名称的副本。
也就是说，我想得到“汤姆”，“汤姆”。
我需要这个的原因是：我犯了一个错误，允许插入重复的名称和电子邮件值。现在我需要删除/更改重复项，所以我需要先找到它们。
当前回答
表结构：
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
解决方案1：
SELECT *,
COUNT(*)
FROM users t1
INNER JOIN users t2
WHERE t1.id > t2.id
AND t1.name = t2.name
AND t1.email=t2.email
解决方案2：
SELECT name,
email,
COUNT(*)
FROM users
GROUP BY name,
email
HAVING COUNT(*) > 1
其他回答
在使用Microsoft Access的情况下，此方法有效：
CREATE TABLE users (id int, name varchar(10), email varchar(50));
INSERT INTO users VALUES (1, 'John', 'asd@asd.com');
INSERT INTO users VALUES (2, 'Sam', 'asd@asd.com');
INSERT INTO users VALUES (3, 'Tom', 'asd@asd.com');
INSERT INTO users VALUES (4, 'Bob', 'bob@asd.com');
INSERT INTO users VALUES (5, 'Tom', 'asd@asd.com');
SELECT name, email, COUNT(*) AS CountOf
FROM users
GROUP BY name, email
HAVING COUNT(*)>1;
DELETE *
FROM users
WHERE id IN (
SELECT u1.id
FROM users u1, users u2
WHERE u1.name = u2.name AND u1.email = u2.email AND u1.id > u2.id
);
感谢Tancrede Chazallet的删除代码。
从表中的重复记录检查。
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);
这是我想出的最简单的办法。它使用一个公共表表达式（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
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
/
试试看：
declare @YourTable table (id int, name varchar(10), email varchar(50))
INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')
SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
输出：
name email CountOf
---------- ----------- -----------
John John-email 2
sam sam-email 2
(2 row(s) affected)
如果您想要重复数据集的ID，请使用以下命令：
SELECT
y.id,y.name,y.email
FROM @YourTable y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email
输出：
id name email
----------- ---------- ------------
1 John John-email
2 John John-email
5 sam sam-email
6 sam sam-email
(4 row(s) affected)
要删除重复项，请尝试：
DELETE d
FROM @YourTable d
INNER JOIN (SELECT
y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
FROM @YourTable y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email
) dt2 ON d.id=dt2.id
WHERE dt2.RowRank!=1
SELECT * FROM @YourTable
输出：
id name email
----------- ---------- --------------
1 John John-email
3 fred John-email
4 fred fred-email
5 sam sam-email
(4 row(s) affected)
