Mysql删除重复的记录，除了一个有最大时间

Question

我有一个包含多个 RFID 记录的表，每个记录都有一个名为时间的列，我想要的是删除重复的 RFID 记录，除了具有最大时间的记录。

表名是 attendance_images(id,RFID,time,year,month,day) 我的查询如下:

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))但是根据条件表有重复记录。

请帮助解决这个问题。

Answer 1

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

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 加入是行不通的。

编辑:

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

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);

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

您可以将 having 子句更改为 having count(*) > 1 以查看是否有具有相同 time 值的副本.

编辑二:

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

t.time < 120000

与:

t.time + 0 < 120000