MySQL中删除重复数据只保留一条-方法2

转: https://www.cnblogs.com/jdbeyond/p/8157224.html

 标题

select * from txt_article group by 字段 having count(字段) > 1字段

select * from txt_article group by art_title having count(art_title) > 1

内容

select * from txt_article group by art_text having count(art_text) > 1

 

 

数据库删除重复记录只保留其中一条

delete from 表 where 字段(递增值) in (select 字段(递增值) from (select 字段(递增值) from 表 where 字段 in (select 字段 from 表 group by 字段 having count(字段)>1) and 字段(递增值) not in (select min(字段(递增值)) from 表 group by 字段 having count(字段)>1)) as tmpresult)

 

delete from txt_article where art_cat_id in (select art_cat_id from (select art_cat_id from txt_article where art_title in (select art_title from txt_article group by art_title having count(art_title)>1) and art_cat_id not in (select min(art_cat_id) from txt_article group by art_title having count(art_title)>1)) as tmpresult)

 

要删除重复的记录,就要先查出重复的记录,这个很容易做到

select * from cqssc group by expect having count(expect) > 1

注意:这是查出所有重复记录的第一条记录,需要保留,因此需要添加查询条件,查出这三条的重复记录

select id,expect from cqssc where expect in (select expect from cqssc group by expect having count(expect)>1) 

and id not in(select min(id) from cqssc group by expect having count(expect)>1)

 以上得到的结果就是我们需要删除的记录!

下面是我最初做错的地方,既然要删除,可能都会想到这样很简单了

报错了!!!在Mysql中是不能删除查询出来的记录的,这里很容易错,当时在这里纠结了很久,而是要通过一张临时表来解决。

 delete from cqssc where id in (select id from (select id from cqssc where expect in 

(select expect from cqssc group by expect having count(expect)>1) and id not in

(select min(id) from cqssc group by expect having count(expect)>1)) as tmpresult)

 再运行试试

删除成功,最后再查询一下看是否还有重复记录

 

相关推荐

网友评论(0)