自己百度
找到了一条sql语句:
DELETE FROM vitae a WHERE (a.peopleId, a.seq) IN ( SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 ) AND rowid NOT IN ( SELECT min(rowid) FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 )
这条语句是在 【MySQL中删除重复数据只保留一条】 这篇文章里找到的。这条sql思路很明显,有以下3步:
SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1
查询出表中重复记录作为条件
SELECT min(rowid) FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1
查询出表中重复记录中ID最小的值为第二个条件
最后根据以上两个条件,删除 除 重复记录中最小ID的其余重复记录
但是很无奈的是,运行这条语句出现了错误,大致报错意思是,不能在查询的时候同时更新这个表。
代码解决
根据上面这个sql语句想到或许可以通过代码的方式,两步来达到同样的目的:
先取出重复的数据集
根据查询到的数据集,循环删除其余的重复数据
想法是有了,写出来也很快,但是一运行吓我一跳,竟然需要 116s 左右,然后自己就想一定要找到可以使用的sql语句,贴一下代码和运行结果:
完美的【去重留一】SQL
最后在一个技术群里得到了完美的答案,看这条sql语句:
DELETE consum_record FROM consum_record, ( SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2 WHERE consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time AND consum_record.id > t2.id;
上面这条sql语句,仔细看一下,揣摩出思路也不难,大概也分为3步来理解:
(SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2
查询出重复记录形成一个集合(临时表t2),集合里是每种重复记录的最小ID
consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time
关联 判断重复基准的字段
根据条件,删除原表中id大于t2中id的记录
看到这个语句的时候,心里想这也太厉害了。这么一个简单的sql语句,竟然可以解决这么复杂的问题,涨姿势了~
运行起来也超级快,原先的代码循环执行,需要 116s 左右,而这里 0.3s 就可以了,厉害了~