我的最后一个查询在这里合并了一些有用的答案-组合group by, count和GROUP_CONCAT。

SELECT GROUP_CONCAT(id), `magento_simple`, COUNT(*) c 
FROM product_variant 
GROUP BY `magento_simple` HAVING c > 1;

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

相应地更改表和列。

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子句执行SELECT操作。假设name是你想要在其中找到重复项的列:

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

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

SELECT varchar_col
FROM table
GROUP BY varchar_col
HAVING COUNT(*) > 1;