在SQL表中查找重复的值 [英] Finding duplicate values in a SQL table

问题描述

很容易找到一个字段的重复项：

It's easy to find duplicates with one field:

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

所以如果我们有一个表

ID   NAME   EMAIL
1    John   [e-mail]
2    Sam    [e-mail]
3    Tom    [e-mail]
4    Bob    [e-mail]
5    Tom    [e-mail]

这个查询将给我们John，Sam，Tom，Tom，因为他们都有相同的 code>。

This query will give us John, Sam, Tom, Tom because they all have the same email.

但是，我想要的是使用相同的电子邮件 和 name 。

However, what I want is to get duplicates with the same email and name.

那就是我想要得到汤姆，汤姆。

That is, I want to get "Tom", "Tom".

我需要这样做的原因：我犯了一个错误，并允许插入重复的名称和电子邮件值。现在我需要删除/更改重复项，所以我需要先找到。

The reason I need this: I made a mistake, and allowed to insert duplicate name and email values. Now I need to remove/change the duplicates, so I need to find them first.

推荐答案

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

只需对两列进行分组。

注意：ANSI标准是在GROUP BY中包含所有非聚合列。
MySQL允许您避免这种情况，但结果是不可预测的：

Note: the ANSI standard is to have all non aggregated columns in the GROUP BY. MySQL allows you to avoid this, but results are unpredictable:

• GROUP BY lname ORDER BY显示错误的结果


• 在没有ANY（）的情况下，最便宜的聚合函数是什么（见接受的注释答案）

• GROUP BY lname ORDER BY showing wrong results
• Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer)

在MySQL中，您需要 sql_mode = only_full_group_by

In MySQL you need sql_mode=only_full_group_by

这篇关于在SQL表中查找重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！