在MySQL中查找列上具有相同值的行 [英] Find rows that have the same value on a column in MySQL

问题描述

在[member]表中，电子邮件列的某些行具有相同的值。

In a [member] table, some rows have the same value for the email column.

login_id | email
---------|---------------------
john     | [e-mail]
peter    | [e-mail]
johnny   | [e-mail]
...

有些人使用不同的login_id，电子邮件地址，在此列上未设置唯一约束。现在我需要找到这些行，看看是否应该删除它们。

Some people used a different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be removed.

我应该使用什么SQL语句来查找这些行？ （MySQL 5）

What SQL statement should I use to find these rows? (MySQL 5)

推荐答案

此查询将提供电子邮件地址列表以及它们的使用次数，

This query will give you a list of email addresses and how many times they're used, with the most used addresses first.

select email, count(*) as c from table
group by email having c > 1
order by c desc

如果你想要完整的行：

select * from table where email in (
    select email from table
    group by email having count(*) > 1
)

这篇关于在MySQL中查找列上具有相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！