一条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步来理解：

1	`(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

1	`consum_record.user_id = t2.user_id` `and` `consum_record.monetary = t2.monetary` `and` `consum_record.consume_time = t2.consume_time` `AND` `<code class="sql plain">consum_record.id > t2.id;</code>`

关联判断重复基准的字段

根据条件，删除原表中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)