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

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。如果id是主键或具有唯一约束,则不存在这种区别,但问题没有对此进行说明。在前一种情况下,您可以使用其他几个答案中给出的代码:

SELECT name, email, COUNT(*)
FROM users
GROUP BY name, email
HAVING COUNT(*) > 1

在后一种情况下,您可以使用:

SELECT name, email, COUNT(DISTINCT id)
FROM users
GROUP BY name, email
HAVING COUNT(DISTINCT id) > 1
ORDER BY COUNT(DISTINCT id) DESC

其他回答

试试看:

                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, COUNT(* ) 
FROM users
GROUP BY name, email
HAVING COUNT(*)>1

试试看:

SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )

通过使用CTE,我们也可以找到类似这样的重复值

with MyCTE
as
(
select Name,EmailId,ROW_NUMBER() over(PARTITION BY EmailId order by id) as Duplicate from [Employees]

)
select * from MyCTE where Duplicate>1

如何获取表中的重复记录

 SELECT COUNT(EmpCode),EmpCode FROM tbl_Employees WHERE Status=1 
 GROUP BY EmpCode HAVING COUNT(EmpCode) > 1