mysql删除日期最大_Mysql删除重复记录,除了一个有最大时间

您还需要匹配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可以具有相同的最大时间,因此仅按时加入不起作用.

编辑：

您应该验证您确实要删除数据：

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子句更改为count(*)> 1,看看你是否有重复项具有相同的时间价值.

编辑二：

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

t.time < 120000

有：

t.time + 0 < 120000