我有一个varchar列的表，我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
我更喜欢使用窗口函数(MySQL 8.0+)来查找副本，因为我可以看到整行:
WITH cte AS (
SELECT *
,COUNT(*) OVER(PARTITION BY col_name) AS num_of_duplicates_group
,ROW_NUMBER() OVER(PARTITION BY col_name ORDER BY col_name2) AS pos_in_group
FROM table
)
SELECT *
FROM cte
WHERE num_of_duplicates_group > 1;
DB小提琴演示
其他回答
SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1
SELECT varchar_col
FROM table
GROUP BY varchar_col
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 column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
