Mysql根据特定列删除相似的行，除了具有最高id的行

Question

我的表在特定列中有重复的行值。我想删除这些行并保留最新 ID 的行。我要检查和比较的列是:

sub_id, spec_id, ex_time

所以，对于这张表

+----+--------+---------+---------+-------+
| id | sub_id | spec_id | ex_time | count |
+----+--------+---------+---------+-------+
|  1 |    100 |     444 | 09:29   |     2 |
|  2 |    101 |     555 | 10:01   |    10 |
|  3 |    100 |     444 | 09:29   |    23 |
|  4 |    200 |     321 | 05:15   |     5 |
|  5 |    100 |     444 | 09:29   |     8 |
|  6 |    101 |     555 | 10:01   |     1 |
+----+--------+---------+---------+-------+

我想得到这个结果

+----+--------+---------+---------+-------+
| id | sub_id | spec_id | ex_time | count |
+----+--------+---------+---------+-------+
|  5 |    100 |     444 | 09:29   |     8 |
|  6 |    101 |     555 | 10:01   |     1 |
+----+--------+---------+---------+-------+

根据 question，我能够构建此查询以从多个列中选择所有重复行

select  t.*
from mytable t join
     (select id, sub_id, spec_id, ex_time, count(*) as NumDuplicates
      from mytable
      group by sub_id, spec_id, ex_time
      having NumDuplicates > 1
     ) tsum
     on t.sub_id = tsum.sub_id and t.spec_id = tsum.spec_id and t.ex_time = tsum.ex_time  

但现在我不确定如何使用删除查询包装此选择以删除具有最高 ID 的行以外的行。作为shown here

Answer 1

• 您可以修改您的子选择查询，以获得每个重复组合的 id 的最大值。
• 现在，在连接到主表时，简单地设置一个条件，即 id 值将不等于最大 id 值。
• 您现在可以从此结果集中删除。

尝试以下操作:

DELETE t 
FROM mytable AS t 
JOIN 
    (SELECT MAX(id) as max_id, 
            sub_id, 
            spec_id, 
            ex_time, 
            COUNT(*) as NumDuplicates
     FROM mytable
     GROUP BY sub_id, spec_id, ex_time
     HAVING NumDuplicates > 1
    ) AS tsum
     ON t.sub_id = tsum.sub_id AND 
        t.spec_id = tsum.spec_id AND 
        t.ex_time = tsum.ex_time AND 
        t.id <> tsum.max_id