在MySQL中寻找重复值

本文翻译自：Finding duplicate values in MySQL

I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. 我有一个带有varchar列的表，我想在此列中查找具有重复值的所有记录。 What is the best query I can use to find the duplicates? 我可以用来查找重复项的最佳查询是什么？

---

#1楼

参考：https://stackoom.com/question/2t7d/在MySQL中寻找重复值

---

#2楼

SELECT t.*,(select count(*) from city as tt
  where tt.name=t.name) as count
  FROM `city` as t
  where (
     select count(*) from city as tt
     where tt.name=t.name
  ) > 1 order by count desc

Replace city with your Table. 用表格替换城市 。 Replace name with your field name 您的字段名称替换名称

---

#3楼

SELECT 
    t.*,
    (SELECT COUNT(*) FROM city AS tt WHERE tt.name=t.name) AS count 
FROM `city` AS t 
WHERE 
    (SELECT count(*) FROM city AS tt WHERE tt.name=t.name) > 1 ORDER BY count DESC

---

#4楼

SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;

---

#5楼

SELECT * 
FROM `dps` 
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)

---

#6楼

根据levik的答案来获取重复行的ID，如果服务器支持，则可以执行GROUP_CONCAT （这将返回用逗号分隔的ID列表）。

SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM documents GROUP BY name HAVING c > 1;