navicat中查重并删除

# 查询所有重复的数据

SELECT * FROM hao123 WHERE ir_url IN
(SELECT ir_url FROM `hao123` GROUP BY ir_url having count(ir_url)>1)

# 只显示某几个字段  提高查询速度
SELECT ir_title,ir_url FROM hao123 WHERE ir_url IN
(SELECT ir_url FROM `hao123` GROUP BY ir_url having count(ir_url)>1)

# 把查询出来的重复行删除
DELETE FROM `hao123` WHERE ir_url IN
(SELECT ir_url FROM
(SELECT ir_url FROM `hao123` GROUP BY ir_url having count(ir_url)>1)a)
AND ir_id NOT IN
(SELECT * FROM
(SELECT min(ir_id) FROM `hao123` GROUP BY ir_url HAVING count(ir_url)>1)b);

 

转载于:https://www.cnblogs.com/kenD/p/11151286.html