在MySQL中查找重复值

我有一个带有varchar列的表，我想找到此列中具有重复值的所有记录。我可以使用的查找重复项的最佳查询是什么？

754092 次浏览
小开
最佳答案

使用GROUP BY子句执行SELECT。假设姓名是您要在其中查找重复项的列：

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

这将返回第一列中具有姓名值的结果，以及该值在第二列中出现的次数的计数。

小开 
SELECT varchar_colFROM tableGROUP BY varchar_colHAVING COUNT(*) > 1;
小开 
SELECT ColumnA, COUNT( * )FROM TableGROUP BY ColumnAHAVING COUNT( * ) > 1
小开

假设您的表名为TableABC，您想要的列是Col，T1的主键是Key。

SELECT a.Key, b.Key, a.ColFROM TableABC a, TableABC bWHERE a.Col = b.ColAND a.Key <> b.Key

与上述答案相比，这种方法的优点在于它给出了密钥。

小开 
SELECT  *FROM    mytable mtoWHERE   EXISTS(SELECT  1FROM    mytable mtiWHERE   mti.varchar_column = mto.varchar_columnLIMIT 1, 1)

此查询返回完整的记录，而不仅仅是不同的varchar_column记录。

这个查询不使用COUNT(*)。如果有很多重复项，COUNT(*)是昂贵的，你不需要整个COUNT(*)，你只需要知道是否有两行具有相同的值。

这是通过相关查询底部的LIMIT 1, 1实现的（本质上意味着“返回第二行”）。EXISTS仅在上述第二行存在时返回true（即至少有两行具有相同的varchar_column值）。

当然，在varchar_column上有一个索引将大大加快这个查询。

小开 
SELECT t.*,(select count(*) from city as ttwhere tt.name=t.name) as countFROM `city` as twhere (select count(*) from city as ttwhere tt.name=t.name) > 1 order by count desc

将城市替换为您的表。将姓名替换为您的字段名

小开 
SELECTt.*,(SELECT COUNT(*) FROM city AS tt WHERE tt.name=t.name) AS countFROM `city` AS tWHERE(SELECT count(*) FROM city AS tt WHERE tt.name=t.name) > 1 ORDER BY count DESC
小开 
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
小开 
SELECT *FROM `dps`WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)
小开

根据levik的答案获取重复行的ID，如果您的服务器支持，您可以执行GROUP_CONCAT（这将返回逗号分隔的id列表）。

SELECT GROUP_CONCAT(id), name, COUNT(*) cFROM documentsGROUP BY nameHAVING c > 1;
小开

要查找员工名称列中有多少条记录是重复的，下面的查询很有帮助；

Select name from employee group by name having count(*)>1;
小开 
Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
小开

要删除具有多个字段的重复行，首先将它们取消到为唯一不同行指定的新唯一键，然后使用“group by”命令删除具有相同新唯一键的重复行：

Create TEMPORARY table tmp select concat(f1,f2) as cfs,t1.* from mytable as t1;Create index x_tmp_cfs on tmp(cfs);Create table unduptable select f1,f2,... from tmp group by cfs;
小开 
CREATE TABLE tbl_master(`id` int, `email` varchar(15));
INSERT INTO tbl_master(`id`, `email`) VALUES(1, 'test1@gmail.com'),(2, 'test2@gmail.com'),(3, 'test1@gmail.com'),(4, 'test2@gmail.com'),(5, 'test5@gmail.com');
QUERY : SELECT id, email FROM tbl_masterWHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1)
小开

我看到了上面的结果，如果您需要检查重复的单列值，查询将正常工作。例如电子邮件。

但是，如果您需要检查更多列并想检查结果的组合，则此查询将正常工作：

SELECT COUNT(CONCAT(name,email)) AS tot,name,emailFROM usersGROUP BY CONCAT(name,email)HAVING tot>1 (This query will SHOW the USER list which ARE greater THAN 1AND also COUNT)
小开

下面将找到所有使用过不止一次的product_id。每个product_id只能得到一条记录。

SELECT product_id FROM oc_product_reward GROUP BY product_id HAVING count( product_id ) >1

代码取自：http://chandreshrana.blogspot.in/2014/12/find-duplicate-records-based-on-any.html

小开

一个非常晚的贡献……以防它能帮助任何人……我的任务是在银行应用程序中找到匹配的交易对（实际上是账户到账户转账的双方），以确定每个账户间转账交易的“从”和“到”，所以我们最终得到了这个：

SELECTLEAST(primaryid, secondaryid) AS transactionid1,GREATEST(primaryid, secondaryid) AS transactionid2FROM (SELECT table1.transactionid AS primaryid,table2.transactionid AS secondaryidFROM financial_transactions table1INNER JOIN financial_transactions table2ON table1.accountid = table2.accountidAND table1.transactionid <> table2.transactionidAND table1.transactiondate = table2.transactiondateAND table1.sourceref = table2.destinationrefAND table1.amount = (0 - table2.amount)) AS DuplicateResultsTableGROUP BY transactionid1ORDER BY transactionid1;

结果是DuplicateResultsTable提供了包含匹配（即重复）事务的行，但它在第二次匹配同一对时也反向提供了相同的事务ID，因此外部SELECT在那里按第一个事务ID分组，这是通过使用LEAST和GREATEST来确保两个transactionid在结果中的顺序总是相同，这使得第一个可以安全地GROUP，从而消除了所有重复的匹配。在不到2秒的时间内运行了近一百万条记录并识别了12000多个匹配。当然，transactionid是主要索引，这真的很有帮助。

小开

我的最后一个问题在这里包含了一些有用的答案-组合group by，count&GROUP_CONCAT。

SELECT GROUP_CONCAT(id), `magento_simple`, COUNT(*) cFROM product_variantGROUP BY `magento_simple` HAVING c > 1;

这提供了两个示例的id（逗号分隔）、我需要的条形码以及重复的数量。

相应地更改表和列。

小开

进一步考虑@maxyfc的答案，我需要找到所有的重复值返回的行，所以我可以在mysql工作台中编辑它们：

SELECT * FROM tableWHERE field IN (SELECT field FROM table GROUP BY field HAVING count(*) > 1) ORDER BY field
小开

我没有看到任何JOIN方法，它们在重复方面有很多用途。

这种方法为您提供了实际的双倍结果。

SELECT t1.* FROM my_table as t1LEFT JOIN my_table as t2ON t1.name=t2.name and t1.id!=t2.idWHERE t2.id IS NOT NULLORDER BY t1.name
小开

我更喜欢使用窗口函数（MySQL 8.0+）来查找重复项，因为我可以看到整行：

WITH cte AS (SELECT *,COUNT(*) OVER(PARTITION BY col_name) AS num_of_duplicates_group,ROW_NUMBER() OVER(PARTITION BY col_name ORDER BY col_name2) AS pos_in_groupFROM table)SELECT *FROM cteWHERE num_of_duplicates_group > 1;

DB小提琴演示

小开

尝试使用此查询：

SELECT name, COUNT(*) value_count FROM company_master GROUP BY name HAVING value_count > 1;
小开

如果要删除重复使用DISTINCT

否则使用此查询：

SELECT users.*,COUNT(user_ID) as user FROM users GROUP BY user_name HAVING user > 1;

小开

要获取包含重复的所有数据，我使用了这个：

SELECT * FROM TableName INNER JOIN(SELECT DupliactedData FROM TableName GROUP BY DupliactedData HAVING COUNT(DupliactedData) > 1 order by DupliactedData)temp ON TableName.DupliactedData = temp.DupliactedData;

TableName=您正在使用的表。

DupliactedData=您要查找的重复数据。

小开

我改进了这个：

SELECTcol,COUNT(col)FROMtable_nameGROUP BY colHAVING COUNT(col) > 1;
小开

作为Levik答案的变体，允许您同时找到重复结果的id，我使用了以下内容：

SELECT * FROM table1 WHERE column1 IN (SELECT column1 AS duplicate_value FROM table1 GROUP BY column1 HAVING COUNT(*) > 1)
小开

感谢@novocaine的精彩回答，他的解决方案对我很有效。我稍微修改了一下，包括了一个百分比的循环值，这在我的情况下是需要的。下面是修改后的版本。它将百分比减少到两个小数位。如果你将，2更改为0，它将不显示小数，将显示1位小数，依此类推。

SELECT GROUP_CONCAT(id), name, COUNT(*) c,COUNT(*) OVER() AS totalRecords,CONCAT(FORMAT(COUNT(*)/COUNT(*) OVER()*100,2),'%') as recurringPecentageFROM tableGROUP BY nameHAVING c > 1

提交答案