MySQL删除重复记录但保留最新记录

Question

我有独特的 id 和 email 字段。电子邮件会重复。我只想保留所有重复项的一个电子邮件地址，但包含最新的 id （最后插入的记录）。

我怎样才能实现这个目标？

Answer 1

假设您的表 test 包含以下数据：

  select id, email
    from test;

ID                     EMAIL                
---------------------- -------------------- 
1                      aaa                  
2                      bbb                  
3                      ccc                  
4                      bbb                  
5                      ddd                  
6                      eee                  
7                      aaa                  
8                      aaa                  
9                      eee 

因此，我们需要找到所有重复的电子邮件并将其全部删除，但最新的 id 除外。
在本例中，aaa、bbb 和 eee 是重复的，因此我们要删除 ID 1、7、2 和 6。

要实现此目的，首先我们需要找到所有重复的电子邮件：

      select email 
        from test
       group by email
      having count(*) > 1;

EMAIL                
-------------------- 
aaa                  
bbb                  
eee  

然后，从这个数据集中，我们需要找到每一封重复电子邮件的最新 ID：

  select max(id) as lastId, email
    from test
   where email in (
              select email 
                from test
               group by email
              having count(*) > 1
       )
   group by email;

LASTID                 EMAIL                
---------------------- -------------------- 
8                      aaa                  
4                      bbb                  
9                      eee                                 

最后，我们现在可以删除所有 Id 小于 LASTID 的电子邮件。所以解决方案是：

delete test
  from test
 inner join (
  select max(id) as lastId, email
    from test
   where email in (
              select email 
                from test
               group by email
              having count(*) > 1
       )
   group by email
) duplic on duplic.email = test.email
 where test.id < duplic.lastId;

我现在没有在这台机器上安装 mySql，但应该可以

更新

上面的删除可以工作，但我找到了一个更优化的版本：

 delete test
   from test
  inner join (
     select max(id) as lastId, email
       from test
      group by email
     having count(*) > 1) duplic on duplic.email = test.email
  where test.id < duplic.lastId;

您可以看到它删除了最旧的重复项，即 1, 7, 2、6：

select * from test;
+----+-------+
| id | email |
+----+-------+
|  3 | ccc   |
|  4 | bbb   |
|  5 | ddd   |
|  8 | aaa   |
|  9 | eee   |
+----+-------+

另一个版本，是 Rene Limon 提供的删除

delete from test
 where id not in (
    select max(id)
      from test
     group by email)

Answer 2

试试这个方法

DELETE t1 FROM test t1, test t2 
WHERE t1.id > t2.id AND t1.email = t2.email

Answer 3

正确的方法是

DELETE FROM `tablename`
  WHERE `id` NOT IN (
    SELECT * FROM (
      SELECT MAX(`id`) FROM `tablename`
        GROUP BY `name`
    ) 
  )

Answer 4

如果您想保留具有最低 id 值的行：

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id > n2.id AND n1.email = n2.email

如果您想保留具有最高 id 值的行：

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id < n2.id AND n1.email = n2.email

或者此查询也可能有帮助

DELETE FROM `yourTableName` 
  WHERE id NOT IN (
    SELECT * FROM (
      SELECT MAX(id) FROM yourTableName 
        GROUP BY name
    ) 
  )

Answer 5

DELETE 
FROM
  `tbl_job_title` 
WHERE id NOT IN 
  (SELECT 
    * 
  FROM
    (SELECT 
      MAX(id) 
    FROM
      `tbl_job_title` 
    GROUP BY NAME) tbl)

修订版和工作版！！！谢谢@Gaurav

Answer 6

我个人对投票最高的两个答案遇到了麻烦。这不是最干净的解决方案，但您可以利用临时表来避免 MySQL 通过连接同一张表进行删除时出现的所有问题。

CREATE TEMPORARY TABLE deleteRows;
SELECT MIN(id) as id FROM myTable GROUP BY myTable.email;

DELETE FROM myTable
WHERE id NOT IN (SELECT id FROM deleteRows);

Answer 7

我必须说，优化版本是一段甜蜜、优雅的代码，即使在 DATETIME 列上执行比较，它也能像魅力一样发挥作用。这是我在脚本中使用的内容，我在脚本中搜索每个 EmployeeID 的最新合同结束日期：

DELETE CurrentContractData
  FROM CurrentContractData
  INNER JOIN (
    SELECT
      EmployeeID,
      PeriodofPerformanceStartDate,
      max(PeriodofPerformanceEndDate) as lastDate,
      ContractID
    FROM CurrentContractData
    GROUP BY EmployeeID
    HAVING COUNT(*) > 1) Duplicate on Duplicate.EmployeeID = CurrentContractData.EmployeeID
    WHERE CurrentContractData.PeriodofPerformanceEndDate < Duplicate.lastDate;

非常感谢！

Answer 8

我想基于表中的多个列删除重复记录，因此这种方法对我有用，

步骤 1 - 从重复记录中获取最大 id 或唯一 id

select *  FROM ( SELECT MAX(id) FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) > 1

步骤 2 - 从表中获取单个记录的 id

select *  FROM ( SELECT id FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) = 1

步骤3 - 从删除到

DELETE FROM `table_name` 
WHERE 
id NOT IN (paste step 1 query) a //to exclude duplicate records
and 
id NOT IN (paste step 2 query) b // to exclude single records

最终查询中排除上述 2 个查询：-

DELETE FROM `table_name` 

WHERE id NOT IN (

select *  FROM ( SELECT MAX(id) FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) > 1) a 
)
and id not in (

select *  FROM ( SELECT id FROM table_name 
group by travel_intimation_id,approved_by,approval_type,approval_status having 
count(*) = 1) b
);

通过此查询，仅删除重复的记录。

Answer 9

DELIMITER // 
CREATE FUNCTION findColumnNames(tableName VARCHAR(255))
RETURNS TEXT
BEGIN
    SET @colNames = "";
     SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.columns
        WHERE TABLE_NAME = tableName
        GROUP BY TABLE_NAME INTO @colNames;
    RETURN @colNames;
END // 
DELIMITER ;

DELIMITER // 
CREATE PROCEDURE deleteDuplicateRecords (IN tableName VARCHAR(255))
BEGIN
    SET @colNames = findColumnNames(tableName);
    SET @addIDStmt = CONCAT("ALTER TABLE ",tableName," ADD COLUMN id INT AUTO_INCREMENT KEY;");
    SET @deleteDupsStmt = CONCAT("DELETE FROM ",tableName," WHERE id NOT IN 
        ( SELECT * FROM ",
            " (SELECT min(id) FROM ",tableName," group by ",findColumnNames(tableName),") AS tmpTable);");
    set @dropIDStmt = CONCAT("ALTER TABLE ",tableName," DROP COLUMN id");

    PREPARE addIDStmt FROM @addIDStmt;
    EXECUTE addIDStmt;

    PREPARE deleteDupsStmt FROM @deleteDupsStmt;
    EXECUTE deleteDupsStmt;

    PREPARE dropIDStmt FROM @dropIDStmt;
    EXECUTE dropIDstmt;

END // 
DELIMITER ;

我创建的很好的存储过程，用于删除表的所有重复记录，而不需要该表上现有的唯一 ID。

CALL deleteDuplicateRecords("yourTableName");

Answer 10

请尝试以下解决方案（基于“@Jose Rui Santos”答案的评论）：

-- Set safe mode to false since;
-- You are using safe update mode and tried to update a table without a WHERE that uses a KEY column
SET SQL_SAFE_UPDATES = 0;

-- Delete the duplicate rows based on the field_with_duplicate_values 
-- Keep the unique rows with the highest id
DELETE FROM table_to_deduplicate
WHERE id NOT IN (
    SELECT * FROM (
        -- Select the highest id grouped by the field_with_duplicate_values
        SELECT MAX(id)
        FROM table_to_deduplicate
        GROUP BY field_with_duplicate_values
    )
    -- Subquery and alias needed since;
    -- You can't specify target table 'table_to_deduplicate' for update in FROM clause
    AS table_sub
);

-- Set safe mode to true
SET SQL_SAFE_UPDATES = 1;

Answer 11

delete  from iamsmsaccountmetadata where 
 id not in (select del.id from ( select iid,max(id) as id
 from iam.iamsmsaccountmetadata
 group by iid
 having count(*) > 1) as del )

这是经过尝试和测试的确切方法