mysql去重(删除重复数据)

作者: dreamfly 分类: mysql 发布时间: 2019-03-27 23:53

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;

mysql可以进行多表关联删除,因此可以创建一个临时表,表中重复数据中选取id值最小的出来,然后删除比最小值大的数据。

delete a from a,b where a.name = b.name and a.id>b.id;

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!