mysql - 删除 "duplicate"条目并保留最新的条目

Question

我忘记向表中添加唯一索引(process_id、result_id):

id, created_at, process_id, result_id, value
1, 2018-11-01, 1, 1, a
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c

现在我有多个 process_id 和 result_id 的相同组合的条目。

为了添加唯一索引，我需要删除重复项，只保留每个进程/结果对的最新条目 (created_at)。

如何轻松识别并删除旧的重复项？

我可以识别所有重复项:

select pr.id, pr.created_at, prd.* 
from process_results pr
join (
    select process_id, result_id
    from process_results 
    group by process_id, result_id
    having count(*) > 1
) prd on pr.request_id = prd.request_id and pr.request_filter_id = prd.request_filter_id

但我不知道如何删除除最新的之外的所有内容

预期结果:

id, created_at, process_id, result_id, value
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c

Answer 1

DELETE t1 
FROM yourTable t1
INNER JOIN yourTable t2 
ON
    t1.created_at < t2.created_at 
AND t1.process_id = t2.process_id 
AND t1.result_id = t2.result_id    
;

识别是一样的

SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2 
ON
    t1.created_at < t2.created_at 
AND t1.process_id = t2.process_id 
AND t1.result_id = t2.result_id