sql 删除表中的重复记录

遇见了表中存在重复的记录的问题，直接写sql删除时最快的，才不要慢慢的复制到excel表中慢慢的人工找呢

如下sql，找出重复的记录，和重复记录中id值最小的记录（表中id为自增长）

select min(id) as id, structsn ,date,userid,starcount,count(structsn) as c
from t_dor_structstar 
where date >= '20160919'
group by structsn ,date,userid,starcount
having count(structsn) > 1

然后就可以直接删除，基本原理就是，找到重复记录的每一条记录，排除掉重复id最小的记录，删除剩余的重复记录。

delete from t_dor_structstar
where id in (
select s.id from t_dor_structstar s,
(
select min(id) as id, structsn ,date,userid,starcount,count(structsn) as c
from t_dor_structstar 
where date >= '20160919'
group by structsn ,date,userid,starcount
having count(structsn) > 1
)a
where
a.date = s.date
and a.structsn = s.structsn
and a.userid = s.userid
and a.starcount = s.starcount
and a.id != s.id
)

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！