我的最后一个查询在这里合并了一些有用的答案-组合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 varchar_col
FROM table
GROUP BY varchar_col
HAVING COUNT(*) > 1;

我的最后一个查询在这里合并了一些有用的答案-组合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 
    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 
    LEAST(primaryid, secondaryid) AS transactionid1,
    GREATEST(primaryid, secondaryid) AS transactionid2
FROM (
    SELECT table1.transactionid AS primaryid, 
        table2.transactionid AS secondaryid
    FROM financial_transactions table1
    INNER JOIN financial_transactions table2 
    ON table1.accountid = table2.accountid
    AND table1.transactionid <> table2.transactionid 
    AND table1.transactiondate = table2.transactiondate
    AND table1.sourceref = table2.destinationref
    AND table1.amount = (0 - table2.amount)
) AS DuplicateResultsTable
GROUP BY transactionid1
ORDER BY transactionid1;

The result is that the DuplicateResultsTable provides rows containing matching (i.e. duplicate) transactions, but it also provides the same transaction id's in reverse the second time it matches the same pair, so the outer SELECT is there to group by the first transaction ID, which is done by using LEAST and GREATEST to make sure the two transactionid's are always in the same order in the results, which makes it safe to GROUP by the first one, thus eliminating all the duplicate matches. Ran through nearly a million records and identified 12,000+ matches in just under 2 seconds. Of course the transactionid is the primary index, which really helped.