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

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

Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1

下面的代码将找到所有使用了不止一次的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

对GROUP BY子句执行SELECT操作。假设name是你想要在其中找到重复项的列:

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

这将返回一个在第一列中包含名称值的结果，以及该值在第二列中出现次数的计数。

我更喜欢使用窗口函数(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小提琴演示

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_master
WHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1)