Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 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小提琴演示

我从下面开始改进:

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 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;

如果你想删除重复使用DISTINCT

否则使用这个查询:

SELECT users.*，COUNT(user_ID) as user FROM users GROUP BY user_name有用户>

SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1