我有一个varchar列的表,我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
要查找Employee中的name列中有多少记录是重复的,下面的查询很有用;
Select name from employee group by name having count(*)>1;
其他回答
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1
我没有看到任何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 name, COUNT(*) value_count FROM company_master GROUP BY name HAVING value_count > 1;
SELECT *
FROM `dps`
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>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