我有一个varchar列的表,我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
如果你想删除重复使用DISTINCT
否则使用这个查询:
SELECT users.*,COUNT(user_ID) as user FROM users GROUP BY user_name有用户>
其他回答
对GROUP BY子句执行SELECT操作。假设name是你想要在其中找到重复项的列:
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
这将返回一个在第一列中包含名称值的结果,以及该值在第二列中出现次数的计数。
我从下面开始改进:
SELECT
col,
COUNT(col)
FROM
table_name
GROUP BY col
HAVING COUNT(col) > 1;
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1
作为利维克的答案的一个变体,它可以让你找到重复结果的id,我使用了以下方法:
SELECT * FROM table1 WHERE column1 IN (SELECT column1 AS duplicate_value FROM table1 GROUP BY column1 HAVING COUNT(*) > 1)
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)