MySQL如何分组查询revision_id最大值对应的行 过滤同组较小值记录

针对你提出的按extinguisher_id分组、取每组revision_id最大的整行、并删除组内冗余记录的需求，以下是适配不同MySQL版本的可直接运行的实现方案：

方法一：MySQL 8.0及以上版本（推荐，性能最优） #

使用窗口函数ROW_NUMBER()实现分组内排序，逻辑清晰、执行效率高。

查询符合要求的结果 #

SELECT id, revision_id, extinguisher_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY extinguisher_id 
            ORDER BY revision_id DESC
        ) AS rn
    FROM extinguisher_revisions
) t
WHERE rn = 1;

删除组内revision_id更小的冗余记录 #

DELETE FROM extinguisher_revisions
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM (
            SELECT 
                id,
                ROW_NUMBER() OVER (
                    PARTITION BY extinguisher_id 
                    ORDER BY revision_id DESC
                ) AS rn
            FROM extinguisher_revisions
        ) t
        WHERE rn > 1
    ) tmp
);

多层嵌套子查询是MySQL的语法要求，用于规避同表查询同时删除的报错问题。

方法二：兼容MySQL 5.x旧版本 #

旧版本MySQL不支持窗口函数，可以通过子查询先拿到每个分组的最大revision_id，再关联原表匹配对应行。

查询符合要求的结果 #

SELECT er.*
FROM extinguisher_revisions er
INNER JOIN (
    SELECT 
        extinguisher_id,
        MAX(revision_id) AS max_revision
    FROM extinguisher_revisions
    GROUP BY extinguisher_id
) tmp
ON er.extinguisher_id = tmp.extinguisher_id
AND er.revision_id = tmp.max_revision;

删除组内revision_id更小的冗余记录 #

DELETE er1
FROM extinguisher_revisions er1
LEFT JOIN (
    SELECT 
        extinguisher_id,
        MAX(revision_id) AS max_revision
    FROM extinguisher_revisions
    GROUP BY extinguisher_id
) er2
ON er1.extinguisher_id = er2.extinguisher_id
AND er1.revision_id = er2.max_revision
WHERE er2.extinguisher_id IS NULL;

注意事项 #

• 如果同一个extinguisher_id分组下存在多条revision_id同为最大值的记录，上述写法会保留所有符合条件的记录。如果需要每组仅保留1条，可以在排序规则中追加id排序，比如ORDER BY revision_id DESC, id DESC即可保留每组最大revision_id下id最大的那条记录。
• 执行删除操作前，务必先运行对应的查询语句确认结果符合预期，避免误删数据。

避坑提醒：不要直接写SELECT id, MAX(revision_id), extinguisher_id FROM extinguisher_revisions GROUP BY extinguisher_id这类语句。在MySQL非严格模式下这类语句虽然能执行，但返回的id值是随机的，无法和最大revision_id对应，会得到错误结果。

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