1. 首页
  2. 问题
  3. MySQL列出所有重复项
小编典典

MySQL列出所有重复项

sql

我在MySQL中有一个这样的表：

ID    name    email
1    john     abc@abc.com
2    johnny   abc@abc.com
3    jim      eee@eee.com
4    Michael  abec@awwbc.com

我怎样才能有MySQL查询来列出像这样的重复项？

重复搜索的结果：

ID    name    email         Duplicate
1    john     abc@abc.com      2
2    johnny   abc@abc.com      2

阅读 213

收藏
2021-03-23

共1个答案

小编典典

SELECT a., b.totalCount AS Duplicate
FROM tablename a
INNER JOIN
(
SELECT email, COUNT() totalCount
FROM tableName
GROUP BY email
) b ON a.email = b.email
WHERE b.totalCount >= 2

为了获得更好的性能，请添加一个INDEXon列EMail。

或者

SELECT  a.*, b.totalCount AS Duplicate
FROM    tablename a
        INNER JOIN
        (
            SELECT  email, COUNT(*) totalCount
            FROM    tableName
            GROUP   BY email
            HAVING  COUNT(*) >= 2
        ) b ON a.email = b.email
2021-03-23