使用一个字段很容易找到重复项：
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
FROM users
WHERE email in
(SELECT email FROM users
GROUP BY 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
);
select name, email
, case
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users
SELECT column_name，COUNT（*）FROM TABLE_name GROUP BY column1，HAVING COUNT；
请尝试
SELECT UserID, COUNT(UserID)
FROM dbo.User
GROUP BY UserID
HAVING COUNT(UserID) > 1
与其他答案不同，您可以查看包含所有列（如果有的话）的整个记录。在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中测试。
