一条sql语句完成MySQL去重留保留一条记录

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 AND consum_record.id > t2.id;

 关联 判断重复基准的字段

根据条件,删除原表中id大于t2中id的记录

看到这个语句的时候,心里想这也太厉害了。这么一个简单的sql语句,竟然可以解决这么复杂的问题,涨姿势了~不得不佩服思路啊!

又找到一条SQL实现去重保留一条记录,分享给大家:

delete from cqssc where id in (select id from (select id from cqssc where expect in (select expect from cqssc group by expect having count(expect)>1) and id not in(select min(id) from cqssc group by expect having count(expect)>1)) as tmpresult)

 

posted @ 2021-12-17 10:31  大象已无形  阅读(2125)  评论(0编辑  收藏  举报