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的记录
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)
Navicat连接MySQL时报10060、1045错误及my.ini位置问题
网友评论