使用一个字段很容易找到重复项：
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(*)
FROM USERS
GROUP BY 1,2
HAVING COUNT(*) > 1
其他回答
试试看：
SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )
试试看：
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
);
与其他答案不同，您可以查看包含所有列（如果有的话）的整个记录。在row_number函数的PARTITION BY部分中，选择所需的唯一/双工列。
SELECT *
FROM (
SELECT a.*
, Row_Number() OVER (PARTITION BY Name, Age ORDER BY Name) AS r
FROM Customers AS a
) AS b
WHERE r > 1;
当您想选择所有字段中的所有重复记录时，可以这样写
CREATE TABLE test (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, c1 integer
, c2 text
, d date DEFAULT now()
, v text
);
INSERT INTO test (c1, c2, v) VALUES
(1, 'a', 'Select'),
(1, 'a', 'ALL'),
(1, 'a', 'multiple'),
(1, 'a', 'records'),
(2, 'b', 'in columns'),
(2, 'b', 'c1 and c2'),
(3, 'c', '.');
SELECT * FROM test ORDER BY 1;
SELECT *
FROM test
WHERE (c1, c2) IN (
SELECT c1, c2
FROM test
GROUP BY 1,2
HAVING count(*) > 1
)
ORDER BY 1;
在PostgreSQL中测试。
这是我想出的最简单的办法。它使用一个公共表表达式（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
/
