我有一个varchar列的表，我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 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
我看到上面的结果和查询将工作良好，如果你需要检查单列值是重复的。比如电子邮件。
但如果你需要检查更多的列，并希望检查结果的组合，那么这个查询将正常工作:
SELECT COUNT(CONCAT(name,email)) AS tot,
name,
email
FROM users
GROUP BY CONCAT(name,email)
HAVING tot>1 (This query will SHOW the USER list which ARE greater THAN 1
AND also COUNT)
我没有看到任何JOIN方法，它在复制方面有很多用途。
这种方法会给您带来实际的双倍结果。
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
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1
Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
