我有一个varchar列的表，我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
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
其他回答
进一步采取@maxyfc的答案，我需要找到所有返回的重复值的行，这样我就可以在MySQL Workbench中编辑它们:
SELECT * FROM table
WHERE field IN (
SELECT field FROM table GROUP BY field HAVING count(*) > 1
) ORDER BY field
下面的代码将找到所有使用了不止一次的product_id。每个product_id只能得到一条记录。
SELECT product_id FROM oc_product_reward GROUP BY product_id HAVING count( product_id ) >1
代码取自:http://chandreshrana.blogspot.in/2014/12/find-duplicate-records-based-on-any.html
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
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
