删除“重复”条目并保留最新条目

axzmvihb 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(332)

我忘了在表中添加唯一索引（进程id、结果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

现在，我有多个条目用于相同的进程id和结果id组合。
为了添加唯一索引，我需要去掉重复项，并且只保留每个进程/结果对的最新条目（created\）。
如何轻松识别和删除旧的副本？
我可以识别所有的重复项：

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

mysql

来源：https://stackoverflow.com/questions/53381290/delete-duplicate-entries-and-keep-latest-one

2条答案

按热度按时间

ejk8hzay1#

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

赞(0）回复(0）举报 2021-06-19

w8biq8rn2#

DELETE bad_rows.* FROM process_results AS bad_rows
                   INNER JOIN (
                      SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
                   ) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id

赞(0）回复(0）举报 2021-06-19

我来回答

删除“重复”条目并保留最新条目

2条答案

相关问题

热门标签

最新问答