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

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 id,name,COUNT(*) from user group by Id,Name having COUNT(*)>1

其他回答

如果要查找重复的数据(通过一个或多个标准),请选择实际的行。

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

删除名称重复的记录

;WITH CTE AS    
(

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

DELETE FROM CTE WHERE T > 1

SELECT id,COUNT(id)FROM table1 GROUP BY id HAVING COUNT;

我认为这可以正确地搜索特定列中的重复值。

试试看:

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

只需在两列上分组。

注意:旧的ANSI标准是在GROUP BY中包含所有非聚合列,但这已经随着“功能依赖性”的思想而改变:

在关系数据库理论中,函数依赖是数据库关系中两组属性之间的约束。换句话说,函数依赖是描述关系中属性之间关系的约束。

支持不一致:

最近的PostgreSQL支持它。SQL Server(截至SQL Server 2017)仍然需要GROUP BY中的所有非聚合列。MySQL是不可预测的,您需要sql_mode=only_full_group_by:GROUP BY lname ORDER BY显示错误结果;在缺少ANY()的情况下,这是最便宜的聚合函数(请参阅公认答案中的注释)。甲骨文还不够主流(警告:幽默,我不了解甲骨文)。