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


当前回答

我没有看到任何JOIN方法,它在复制方面有很多用途。

这种方法会给您带来实际的双倍结果。

SELECT t1.* FROM my_table as t1 
LEFT JOIN my_table as t2 
ON t1.name=t2.name and t1.id!=t2.id 
WHERE t2.id IS NOT NULL 
ORDER BY t1.name

其他回答

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

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

我更喜欢使用窗口函数(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_group
  FROM table
)
SELECT *
FROM cte
WHERE num_of_duplicates_group > 1;

DB小提琴演示

试着使用这个查询:

SELECT name, COUNT(*) value_count FROM company_master GROUP BY name HAVING value_count > 1;
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1