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

问题描述

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

It's easy to find duplicates with one field:

SELECT email, 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]

这个查询将给我们约翰、山姆、汤姆、汤姆，因为他们都有相同的email.

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

但是，我想要的是使用相同的 email 和 name 获取重复项.

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

也就是说，我想得到汤姆"、汤姆".

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

我需要这个的原因:我犯了一个错误，允许插入重复的 name 和 email 值.现在我需要删除/更改重复项，所以我需要先找到它们.

The reason I need this: I made a mistake, and allowed inserting 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 中包含所有非聚合列，但这已经随着 "函数依赖":

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":

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

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.

支持不一致:

• 最近的 PostgreSQL 支持它.
• SQL Server(与 SQL Server 2017 相同)仍然需要 GROUP BY 中的所有非聚合列.
• MySQL 是不可预测的，您需要 sql_mode=only_full_group_by:
  • GROUP BY lname ORDER BY 显示错误结果;
  • 其中在没有 ANY() 的情况下是最便宜的聚合函数(请参阅已接受答案中的评论).
  • Recent PostgreSQL supports it.
  • SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
  • MySQL is unpredictable and you need sql_mode=only_full_group_by:
    • 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).

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