在 MySQL 中查找重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/688549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding duplicate values in MySQL
提问by Jon Tackabury
I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. What is the best query I can use to find the duplicates?
我有一个带有 varchar 列的表，我想在此列中查找所有具有重复值的记录。我可以用来查找重复项的最佳查询是什么？
回答by levik
Do a SELECTwith a GROUP BYclause. Let's say nameis the column you want to find duplicates in:
SELECT用一个GROUP BY子句做一个。假设name是您要在其中查找重复项的列：
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
This will return a result with the namevalue in the first column, and a count of how many times that value appears in the second.
这将返回一个在第一列中具有名称值的结果，以及该值在第二列中出现的次数的计数。
回答by maxyfc
SELECT varchar_col
FROM table
GROUP BY varchar_col
HAVING COUNT(*) > 1;
回答by Quassnoi
SELECT *
FROM mytable mto
WHERE EXISTS
(
SELECT 1
FROM mytable mti
WHERE mti.varchar_column = mto.varchar_column
LIMIT 1, 1
)
This query returns complete records, not just distinct varchar_column's.
此查询返回完整记录，而不仅仅是 distinct varchar_column。
This query doesn't use COUNT(*). If there are lots of duplicates, COUNT(*)is expensive, and you don't need the whole COUNT(*), you just need to know if there are two rows with same value.
此查询不使用COUNT(*). 如果有很多重复项，COUNT(*)成本很高，并且您不需要整个COUNT(*)，您只需要知道是否有两行具有相同的值。
Having an index on varchar_columnwill, of course, speed up this query greatly.
varchar_column当然，有一个索引会大大加快这个查询。
回答by Matt Rardon
Building off of levik's answer to get the IDs of the duplicate rows you can do a GROUP_CONCATif your server supports it (this will return a comma separated list of ids).
GROUP_CONCAT如果您的服务器支持，您可以根据 levik 的答案获取重复行的 ID （这将返回一个逗号分隔的 ID 列表）。
SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM documents GROUP BY name HAVING c > 1;
回答by TechTravelThink
Assuming your table is named TableABC and the column which you want is Col and the primary key to T1 is Key.
假设您的表名为 TableABC，您想要的列是 Col，T1 的主键是 Key。
SELECT a.Key, b.Key, a.Col
FROM TableABC a, TableABC b
WHERE a.Col = b.Col
AND a.Key <> b.Key
The advantage of this approach over the above answer is it gives the Key.
与上述答案相比，这种方法的优势在于它提供了密钥。
回答by strustam
SELECT *
FROM `dps`
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)
回答by user5599549
To find how many records are duplicates in name column in Employee, the query below is helpful;
要查找 Employee 的 name 列中有多少记录重复，下面的查询很有帮助；
Select name from employee group by name having count(*)>1;
回答by udi
to get all the data that contains duplication i used this:
为了获取包含重复的所有数据，我使用了这个：
SELECT * FROM TableName INNER JOIN(
SELECT DupliactedData FROM TableName GROUP BY DupliactedData HAVING COUNT(DupliactedData) > 1 order by DupliactedData)
temp ON TableName.DupliactedData = temp.DupliactedData;
TableName = the table you are working with.
TableName = 您正在使用的表。
DupliactedData = the duplicated data you are looking for.
DupliactedData = 您要查找的重复数据。
回答by Jonathan
My final query incorporated a few of the answers here that helped - combining group by, count & GROUP_CONCAT.
我的最后一个查询在这里包含了一些有帮助的答案 - 组合了 group by、count 和 GROUP_CONCAT。
SELECT GROUP_CONCAT(id), `magento_simple`, COUNT(*) c
FROM product_variant
GROUP BY `magento_simple` HAVING c > 1;
This provides the id of both examples (comma separated), the barcode I needed, and how many duplicates.
这提供了两个示例的 id（逗号分隔）、我需要的条形码以及重复的数量。
Change table and columns accordingly.
相应地更改表和列。
回答by Adam Fischer
I am not seeing any JOIN approaches, which have many uses in terms of duplicates.
我没有看到任何 JOIN 方法，它们在重复项方面有很多用途。
This approach gives you actual doubled results.
这种方法为您提供了实际翻倍的结果。
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
