MySQL删除重复的记录，除了一个有最大时间 [英] Mysql delete duplicate records except one has max time

问题描述

我有一个表，该表具有多个RFID记录，每个记录都有一个称为时间的列，我要删除重复的RFID记录，但其中一个记录的时间最长.

I have a table which has multiple RFID records and each record has a column called time and what I want is deleting duplicate RFID recods except one which has max time.

表名称是Attenance_images(id，RFID，time，year，month，day，day)，我的查询如下:

table name is attendance_images(id,RFID,time,year,month,day) and my query is as below:

DELETE t FROM attendance_images AS t LEFT JOIN (
   SELECT max( t1.time ) AS time
   FROM attendance_images AS t1
   WHERE t1.year=2016
   AND t1.month=8
   AND t1.day=4
   AND t1.time < 120000
   GROUP BY t1.RFID
) keep ON t.time = keep.time
WHERE keep.time IS NULL
AND t.year =2016
AND t.month =8
AND t.day =4
AND t.time < 120000

查询效果为(0 rows deleted. (Query took 0.0034 sec))，但该表根据条件具有重复的记录.

The query effect is (0 rows deleted. (Query took 0.0034 sec)) but the table has duplicate records according condition.

请帮助解决此问题.

推荐答案

您还需要在RFID上进行匹配:

You need to match on RFID as well:

DELETE t
    FROM attendance_images AS t LEFT JOIN
         (SELECT RFID, max( t1.time ) AS time
          FROM attendance_images AS t1
          WHERE t1.year = 2016 AND t1.month = 8 AND t1.day = 4 AND t1.time < 120000
          GROUP BY t1.RFID
         ) keep
         ON t.time = keep.time AND t.RFID = keep.RFID
WHERE keep.time IS NULL AND
      t.year = 2016 AND t.month = 8 AND t.day = 4 AND t.time < 120000;

显然，一个以上的RFID可以具有相同的最大时间，因此仅在time上加入是行不通的.

Apparently, more than one RFID can have the same maximum time, so joining only on time doesn't work.

您应该验证您是否确实有数据要删除:

You should verify that you actually have data to delete:

select ai.rfid, max(ai.time), min(ai.time), count(*)
from attendance_images ai
where ai.year = 2016 AND ai.month = 8 AND ai.day = 4 AND ai.time < 120000
group by ai.rfid
having min(ai.time) < max(ai.time);

我的猜测是，这将不返回任何行，表明您没有此类重复项.

My guess is that this will return no rows, indicating that you have have no such duplicates.

您可以将having子句更改为having count(*) > 1，以查看是否存在具有相同time值的重复项.

You can change the having clause to having count(*) > 1 to see if you have duplicates that have the same time value.

编辑II:

查询中值的缩进表明time存储为字符串而不是整数.您可以尝试替换条件:

The indentation of the values in the query suggest that time is stored as a string, not an integer. You might try replacing the condition:

t.time < 120000

具有:

t.time + 0 < 120000

这篇关于MySQL删除重复的记录，除了一个有最大时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！