我有一个varchar列的表，我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
其他回答
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
我更喜欢使用窗口函数(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
col,
COUNT(col)
FROM
table_name
GROUP BY col
HAVING COUNT(col) > 1;
下面的代码将找到所有使用了不止一次的product_id。每个product_id只能得到一条记录。
SELECT product_id FROM oc_product_reward GROUP BY product_id HAVING count( product_id ) >1
代码取自:http://chandreshrana.blogspot.in/2014/12/find-duplicate-records-based-on-any.html
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
用你的表格替换城市。 将name替换为字段名
