简介
backends_alertdata 这个表格里面有很多的重复数据,其中每一条重复数据的fingerprint 字段都是一样的,aid 这个是自增字段,是主键。现在需要将这些重复数据删除,但是只保留aid 最大的一条数据
SQL语句:
delete from `backends_alertdata`
where
fingerprint in (
select a.fingerprint from
(SELECT fingerprint FROM `backends_alertdata` where alertstatus='masking' GROUP BY instance HAVING COUNT(*) >1) as a
)
and
aid not in (
select b.aid from
(select max(aid) as aid FROM `backends_alertdata` where alertstatus='masking' GROUP BY instance HAVING COUNT(*) >1) as b
)