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);
复制代码

 

posted on   KD_131  阅读(2197)  评论(0编辑  收藏  举报
努力加载评论中...

点击右上角即可分享
微信分享提示