MySQL去重,一条SQL语句完美解决【去重留一】

kyle 2017-12-05 1684次浏览 0条评论 0 打赏作者 0 0

此处以某消费记录表(consume_record)为例,SQL语句如下:

DELETE consume_record
FROM
    consume_record, 
    (
        SELECT
            min(id) id,
            user_id,
            monetary,
            consume_time
        FROM
            consume_record
        GROUP BY
            user_id,
            monetary,
            consume_time
        HAVING
            count(*) > 1
    ) t2
WHERE
    consume_record.user_id = t2.user_id 
    and consume_record.monetary = t2.monetary
    and consume_record.consume_time  = t2.consume_time
AND consume_record.id > t2.id;

SQL语句分析:

1、查询出重复记录形成一个集合(临时表t2),集合里是每种重复记录的最小ID

(SELECT min(id) id, user_id, monetary, consume_time FROM consume_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2

2、关联<判断重复基准的字段

consume_record.user_id = t2.user_id and consume_record.monetary = t2.monetary and consume_record.consume_time  = t2.consume_time

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

DELETE consume_record FROM ... WHERE ... AND consume_record.id > t2.id;

测试效果:


0

0 条评论

    没有找到数据。

发表评论

kyle
土豪

kyle

注册时间:2016-10-28
最后登录:16天前
发布
带到手机上看