sql - 在SQL表中查找重复值(Finding duplicate values in a SQL table)

Question

It's easy to find duplicates with one field:

(使用一个字段很容易找到duplicates ：)

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

So if we have a table

(所以，如果我们有一张桌子)

ID   NAME   EMAIL
1    John   [email protected]
2    Sam    [email protected]
3    Tom    [email protected]
4    Bob    [email protected]
5    Tom    [email protected]

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

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

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

(但是，我想要的是使用相同的email和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.

(我需要这个的原因：我犯了一个错误，并允许插入重复的name和email值。)

Now I need to remove/change the duplicates, so I need to find them first.

(现在我需要删除/更改重复项，所以我需要先找到它们。)

  ask by Alex translate from so

Answer 1

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

Simply group on both of the columns.

(只需在两个列上分组。)

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency" :

(注意：旧的ANSI标准是在GROUP BY中包含所有非聚合列，但这已经改变了“功能依赖”的概念 ：)

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database.

(在关系数据库理论中，函数依赖性是来自数据库的关系中的两组属性之间的约束。)

In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

(换句话说，函数依赖是描述关系中属性之间关系的约束。)

Support is not consistent:

(支持不一致：)

• Recent PostgreSQL supports it .

  (最近的PostgreSQL 支持它 。)

• SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.

  (SQL Server（与SQL Server 2017一样）仍然需要GROUP BY中的所有非聚合列。)

• MySQL is unpredictable and you need sql_mode=only_full_group_by :

  (MySQL是不可预测的，你需要sql_mode=only_full_group_by ：)

  • GROUP BY lname ORDER BY showing wrong results ;

    (GROUP BY lname ORDER BY显示错误的结果 ;)

  • Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).

    (在没有ANY（）的情况下，哪个是最便宜的聚合函数 （参见接受答案中的注释）。)

• Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

  (Oracle不够主流（警告：幽默，我不了解Oracle）。)