如何实现SQL通用删除表中Message_id重复行并保留任意一行？

我有一个仅包含两列的表：

• Message_id
• Id（主键）

需求：删除表中Message_id重复的行，但保留其中任意一行（或第一行）。

我已经能通过以下查询找出所有重复的Message_id：

select message_id
from myDb.My_Tb
group by message_id 
having count(message_id) > 1

我曾尝试用行号定位重复行，但使用MOD(r,2) = 0时无法精准定位要删除的行，得到的是随机行：

select row_number() over() as rn, id, message_id 
from MyDb.My_Tb
where message_id in (
    select message_id
    from MyDb.My_Tb
    group by message_id 
    having count(message_id) > 1
)
order by message_id asc

目前我用下面的查询实现了需求，但该方案仅适用于每组重复数为2的情况：

delete from MyDb.My_Tb where id in
(select id  from 
(select row_number () over() as rownum, id , message_id
from

(
    (select id ,  message_id from MyDb.My_Tb
     where message_id in

        (select message_id
        from MyDb.My_Tb 
        group by message_id 
        having count(message_id)>1)

     order by message_id asc) 
) as dummy
) as dummy1

where mod(rownum,2) = 0)

请问是否存在适用于任意N个重复情况的通用解决方案？

要处理任意数量的重复Message_id，核心是给每个Message_id分组内的行独立分配行号，而非全局统一编号。利用ROW_NUMBER()窗口函数配合PARTITION BY子句即可实现，具体方案如下：

方法1：子查询定位删除行 #

DELETE FROM MyDb.My_Tb
WHERE Id IN (
    SELECT Id
    FROM (
        SELECT 
            Id,
            ROW_NUMBER() OVER (PARTITION BY Message_id ORDER BY Id) AS rn
        FROM MyDb.My_Tb
    ) t
    WHERE rn > 1
);

• 逻辑说明：PARTITION BY Message_id将相同Message_id的行归为一组，ORDER BY Id确保每组内按主键升序排序（保留主键最小的行，即最早插入的行）；若无需固定保留顺序，可去掉ORDER BY（但建议指定排序规则保证结果可控）。

方法2：CTE（公共表达式）简化写法 #

如果你的数据库支持CTE（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用更清晰的写法：

WITH DuplicateRows AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY Message_id ORDER BY Id) AS rn
    FROM MyDb.My_Tb
)
DELETE FROM MyDb.My_Tb
WHERE Id IN (SELECT Id FROM DuplicateRows WHERE rn > 1);

• 部分数据库（如MySQL）支持直接从CTE中删除，可简化为：

WITH DuplicateRows AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY Message_id ORDER BY Id) AS rn
    FROM MyDb.My_Tb
)
DELETE FROM DuplicateRows
WHERE rn > 1;

核心要点 #

• ROW_NUMBER() OVER (PARTITION BY message_id ...)是关键：它会为每个message_id分组单独生成从1开始的行号，无论每组有多少重复行，行号>1的都是需要删除的重复项。
• 若要保留每组的最后一行，只需将ORDER BY Id改为ORDER BY Id DESC即可。

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