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 关联 判断重复基准的字段

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

看到这个语句的时候，心里想这也太厉害了。这么一个简单的sql语句，竟然可以解决这么复杂的问题，涨姿势了~