MySQL删除具有相同列值的重复行 [英] MySQL Delete duplicate rows that have same column value

问题描述

我正在清理一些数据，我想通过查找具有相同图片ID"和日期"值的记录来删除重复的行:

I am doing some data clean up and I would like to remove duplicate rows by finding records that have the same "picture id" and "date" values:

示例:

picture_id - 2 date - "13-Jul-18"
picture_id - 2 date - "13-Jul-18"
picture_id - 2 date - "13-Jul-18"
picture_id - 2 date - "13-Jul-18"

DELETE FROM `pictures` WHERE `picture_id` = '2' AND `date` = '13-Jul-18'

表格列(按顺序):ID(主键)，picture_id，日期，关注者

Table columns (in order): ID (primary key), picture_id, date, followers

我只希望删除重复记录中的所有记录，但其中之一除外.哪一个都没关系.我该怎么做?

I would like to only delete all but one of the duplicate records. It does not matter which one. How can I accomplish this?

推荐答案

在MySQL中，您可以使用JOIN保留最小(或最大)的id:

In MySQL, you can keep the smallest (or biggest) id using JOIN:

DELETE p
    FROM pictures p JOIN
         (SELECT p.picture_id, p.date, MIN(id) as min_id
          FROM pictures p
          WHERE p.picture_id = 2 AND p.date = '2018-07-13' 
          GROUP BY p.picture_id
         ) pp
         ON p.picture_id = pp.picture_id AND p.date = pp.date AND p.id > p.min_id;

这篇关于MySQL删除具有相同列值的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！