mysql 存储去重_mysql去重之实战总结

一、有主键去重

a、单字段重复

查询重复记录(一条)

select aid from ab group by aid having count(*)>1

查询重复记录(全部)

select * from ab t where t.aid in(select aid from ab group by aid having count(*)>1);

删除重复记录保留一条

delete from ab where aid in(

select aid from ab group by aid having count(*)>1

)and id not in(

select max(id)as id from ab group by aid having count(*)>1

)

刚开始想到这个sql，结果

于是经过查资料，sql整改

delete from ab where aid in(

select t.aid from(

select aid from ab group by aid having count(*)>1

) t

)and id not in(

select t.id from(

select max(id) as id from ab group by aid having count(*)>1

) t

)

去重成功！

b、多字段重复

table ab

查询重复记录(一条)

select aid,bid from ab group by aid,bid having count(*)>1

查询重复记录(全部)

select * from ab t where(t.aid,t.bid)in(select aid,bid from ab group by aid,bid having count(*)>1);

删除重复记录保留一条

delete from ab where (aid,bid) in (

select t.aid,t.bid from (

select aid,bid from ab group by aid,bid having count(*)>1

) t

) and id not in (

select t.id from (

select max(id) as id from ab group by aid,bid having count(*)>1

) t

)

二、无主键去重

单字段、多字段

table ab

删除重复记录保留一条

--建临时表插入去重数据

create table ab_temp (select * from ab group by aid,bid having count(*)>1);

--删除重复数据

delete from ab where (aid,bid) in (

select t.aid,t.bid from (

select aid,bid from ab group by aid,bid having count(*)>1

) t

);

--插入去重数据

insert into ab select * from ab_temp;

--删除临时表

drop table ab_temp;