我有一个varchar列的表,我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
CREATE TABLE tbl_master
(`id` int, `email` varchar(15));
INSERT INTO tbl_master
(`id`, `email`) VALUES
(1, 'test1@gmail.com'),
(2, 'test2@gmail.com'),
(3, 'test1@gmail.com'),
(4, 'test2@gmail.com'),
(5, 'test5@gmail.com');
QUERY : SELECT id, email FROM tbl_master
WHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1)
其他回答
我看到上面的结果和查询将工作良好,如果你需要检查单列值是重复的。比如电子邮件。
但如果你需要检查更多的列,并希望检查结果的组合,那么这个查询将正常工作:
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)
Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
根据levik的回答来获取重复行的id,如果服务器支持的话,可以执行GROUP_CONCAT(这将返回一个以逗号分隔的id列表)。
SELECT GROUP_CONCAT(id), name, COUNT(*) c
FROM documents
GROUP BY name
HAVING c > 1;
要查找Employee中的name列中有多少记录是重复的,下面的查询很有用;
Select name from employee group by name having count(*)>1;
对GROUP BY子句执行SELECT操作。假设name是你想要在其中找到重复项的列:
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
这将返回一个在第一列中包含名称值的结果,以及该值在第二列中出现次数的计数。