我有一个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 column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
SELECT *
FROM `dps`
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)
感谢@novocaine的精彩回答，他的解决方案对我很有效。我稍微改变了它，以包括一个百分比的循环值，这在我的例子中是需要的。以下是修改后的版本。它将百分比减少到小数点后两位。如果你把2改成0，它就不会显示小数，改成1，它就会显示一位小数，以此类推。
SELECT GROUP_CONCAT(id), name, COUNT(*) c,
COUNT(*) OVER() AS totalRecords,
CONCAT(FORMAT(COUNT(*)/COUNT(*) OVER()*100,2),'%') as recurringPecentage
FROM table
GROUP BY name
HAVING c > 1
为了获得所有包含复制的数据，我使用了以下方法:
SELECT * FROM TableName INNER JOIN(
SELECT DupliactedData FROM TableName GROUP BY DupliactedData HAVING COUNT(DupliactedData) > 1 order by DupliactedData)
temp ON TableName.DupliactedData = temp.DupliactedData;
TableName =您正在使用的表。
DupliactedData =您正在寻找的重复数据。
如果你想删除重复使用DISTINCT
否则使用这个查询:
SELECT users.*，COUNT(user_ID) as user FROM users GROUP BY user_name有用户>
