我有一个varchar列的表，我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
SELECT *
FROM `dps`
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>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;
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
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
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1
