SQL删除重复行：保留最大job_id的唯一行，删除其余重复项

我的job_posts表存在重复数据，已通过以下SQL查询出这些重复项：

select p.*, j.job_id from (
    select count(job_id) cnt, title, email, msg_no from (
        select job_id, title, email, msg_no from job_posts order by job_id desc limit 500) as t 
    group by title, email, msg_no ) p 
left join job_posts j on p.title = j.title and p.email = j.email 
    and p.msg_no = j.msg_no where p.cnt >1

查询结果如下：

cnt  |  title          |    email         | msg_no | job_id
2    | some title      | simon@domain.com |  123   | 210
2    | some title      | simon@domain.com |  123   | 209
2    | some title      | simon@domain.com |  123   | 208
2    | another title   | bob@domain.com   |  243   | 329
2    | another title   | bob@domain.com   |  243   | 328

我需要删除每个唯一(title,email,msg_no)组中job_id小于该组最大值的行，即删除以下行：

cnt  |  title          |    email         | msg_no | job_id
2    | some title      | simon@domain.com |  123   | 209
2    | some title      | simon@domain.com |  123   | 208
2    | another title   | bob@domain.com   |  243   | 328

解决方案 #

方法一：子查询筛选保留行

先找出每个分组的最大job_id，再删除不在这个集合内的重复行：

DELETE FROM job_posts
WHERE job_id NOT IN (
    SELECT max_job_id
    FROM (
        SELECT MAX(job_id) AS max_job_id
        FROM job_posts
        GROUP BY title, email, msg_no
    ) AS sub_query
)
-- 若仅需处理查询范围内的前500条数据，可添加以下条件
AND job_id IN (
    SELECT job_id
    FROM job_posts
    ORDER BY job_id DESC LIMIT 500
)

方法二：JOIN关联删除（效率更高）

通过关联分组后的最大job_id，直接删除符合条件的行：

DELETE j1
FROM job_posts j1
JOIN (
    SELECT title, email, msg_no, MAX(job_id) AS max_job_id
    FROM job_posts
    GROUP BY title, email, msg_no
) j2 ON j1.title = j2.title 
    AND j1.email = j2.email 
    AND j1.msg_no = j2.msg_no
WHERE j1.job_id < j2.max_job_id
-- 若仅需处理查询范围内的前500条数据，可添加以下条件
AND j1.job_id IN (
    SELECT job_id
    FROM job_posts
    ORDER BY job_id DESC LIMIT 500
)

重要提醒：执行删除前，建议将DELETE替换为SELECT j1.*（方法二）或SELECT *（方法一），验证待删除的行是否符合预期，避免误删数据。

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