在MySQL中查找具有相同列值的行

Question

在一个 [member] 表中，一些行在 email 列中有相同的值。

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

有些人使用了不同的login_id，但是相同的电子邮件地址，并且该列上没有设置唯一约束。现在我需要找到这些行，并确定它们是否应该被删除。

我该使用哪个SQL语句来查找这些行？（MySQL 5）

- bobo

Answer 1

此查询将为您提供一个电子邮件地址列表及其使用次数，最常用的地址排在第一位。

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
)

- Scott Saunders

---

count(1) 的效果同样好，并且性能更高。 （从[so]学到的技巧;-)） - jpaugh

@jpaugh可能不想使用count(1)。https://dev59.com/PXE85IYBdhLWcg3wkkfK - Storm

也曾在SQLite工作过。谢谢！ - vss

@jpaugh 现在使用 count(*) 和 count(1) 查询语句性能上已经没有区别了，DBMS的查询分析器都非常智能化，不会加载整行数据来执行 count(*)。你可以自己测试一下（别忘了在测试前禁用缓存）。 - kdmitry

如何在group by中使用多列并且保留完整的行？ - undefined

Answer 2

select email from mytable group by email having count(*) >1

- HLGEM

---

被认可的答案在Postgres上不能使用，而这个可以。 - azio

@HLGEM：http://stackoverflow.com/questions/41359879/postgresql-get-records-having-similar-column-values - Syed Asad Abbas Zaidi

Answer 3

以下是查询多个 login_id 所使用的相同 email 的语句：

SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1

您需要进行第二个（嵌套的）查询，以通过 email 获取 login_id 列表。

- Ivan Nevostruev

Answer 4

被接受的答案的第一部分并不适用于MSSQL。
这个对我有用：

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

- Sergey Makhonin

Answer 5

如果您的电子邮件列包含空值，请使用此选项。

 select * from table where email in (
    select email from table group by email having count(*) > 1 and email != ''
    )

- ramesh kumar

Answer 6

谢谢大家 :-) 我使用了下面的代码，因为我只关心这两列而不是其他的。效果很好。

  select email, login_id from table
    group by email, login_id
    having COUNT(email) > 1

- Libertine Makinta

---

在这种情况下，COUNT(email)始终为1，因此您的查询将返回空。 - jutky

不，实际上查询给了我所需的数据，即具有相同电子邮件的电子邮件和登录名。 - Libertine Makinta

如果您按电子邮件和登录ID分组，则将计算相同电子邮件和登录的行数，这些在您的示例中是不同的，因此计数始终为1。这是带有您查询的fiddle，返回0行：http://sqlfiddle.com/#!9/4bbcaf/3 - jutky

Answer 7

我知道这是一个非常老的问题，但这更多地是为了帮助有同样问题的人，我认为这更准确地符合所需。

SELECT * FROM member WHERE email = (Select email From member Where login_id = [e-mail]) 

这将返回所有登录ID值为[e-mail]的记录。

- Marc L

Answer 8

这是最佳实践

截图

SELECT RollId, count(*) AS c 
    FROM `tblstudents` 
    GROUP BY RollId 
    HAVING c > 1 
    ORDER BY c DESC

- Adeleye Ayodeji

Answer 9

使用内部选择查询条件，按您想要的方式获取整个记录。最初的回答。

SELECT *
FROM   member
WHERE  email IN (SELECT email
                 FROM   member
                 WHERE  login_id = [e-mail]) 

- Suba Karthikeyan

Answer 10

虽然来这个帖子有点晚了，但我曾经遇到过类似的情况，以下语句适用于MySQL。这个查询也会返回所有符合重复电子邮件条件的行。

SELECT * FROM TABLE WHERE EMAIL IN 
       (SELECT * FROM 
            (SELECT EMAIL FROM TABLE GROUP BY EMAIL HAVING COUNT(EMAIL) > 1) 
        AS X);

- sunitkatkar