我有一个varchar列的表,我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?


当前回答

要查找Employee中的name列中有多少记录是重复的,下面的查询很有用;

Select name from employee group by name having count(*)>1;

其他回答

进一步采取@maxyfc的答案,我需要找到所有返回的重复值的行,这样我就可以在MySQL Workbench中编辑它们:

SELECT * FROM table
   WHERE field IN (
     SELECT field FROM table GROUP BY field HAVING count(*) > 1
   ) ORDER BY field

作为利维克的答案的一个变体,它可以让你找到重复结果的id,我使用了以下方法:

SELECT * FROM table1 WHERE column1 IN (SELECT column1 AS duplicate_value FROM table1 GROUP BY column1 HAVING COUNT(*) > 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;

试着使用这个查询:

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