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

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.

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

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

Please help to fix this issue.

解决方案

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;

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

EDIT:

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.

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

EDIT II:

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

with:

t.time + 0 < 120000