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;