MySQL删除重复行并保留另一列最大值对应行的操作方法咨询

前置提醒 #

操作删除数据前务必先备份整表，避免数据丢失：

CREATE TABLE C2_backup_2024 AS SELECT * FROM C2;

解法1：MySQL 8.0+版本（支持窗口函数，推荐） #

用ROW_NUMBER()窗口函数给每个src + dst分组内的行排序，durations倒序排列后，每组仅保留编号为1的行，其余全部删除：

DELETE t1
FROM C2 t1
INNER JOIN (
    SELECT 
        file_id,
        -- 按src、dst分组，durations降序，同最大值时默认按file_id降序，可自定义排序规则
        ROW_NUMBER() OVER(PARTITION BY src, dst ORDER BY durations DESC, file_id DESC) AS row_num
    FROM C2
) t2 ON t1.file_id = t2.file_id
WHERE t2.row_num > 1;

如果要提前校验要删除的行是否正确，把第一行的DELETE t1改成SELECT t1.*即可，确认无误再执行删除。如果同组durations最大值的行有多个，想指定保留规则，只需要调整窗口函数ORDER BY后面的排序条件即可，比如想保留最新生成的行，就改成ORDER BY durations DESC, date_time DESC。 #

解法2：MySQL 5.x版本（无窗口函数支持） #

先查询每个分组需要保留的行的主键id，再删除其余行：

-- 1、创建临时表存储每个分组要保留的file_id，同最大值时默认保留file_id最小的行，可替换为MAX(file_id)
CREATE TEMPORARY TABLE keep_file_ids
SELECT MIN(file_id) AS keep_id
FROM C2
WHERE (src, dst, durations) IN (
    SELECT src, dst, MAX(durations)
    FROM C2
    GROUP BY src, dst
)
GROUP BY src, dst;

-- 2、执行删除，不在保留列表内的行全部删除
DELETE FROM C2
WHERE file_id NOT IN (SELECT keep_id FROM keep_file_ids);

内容的提问来源于stack exchange，提问作者Bata