MySQL含ID的多对多关联：如何删除除最新条目外的所有记录？

核心思路 #

先定位每个jobId对应的最新那条记录，再删除jobEntries中不属于这个集合的所有记录。以下是不同数据库的实现方案：

MySQL 实现 #

方案1：通过MAX(createdAt)定位最新记录

适合不存在同一jobId下多条记录createdAt完全相同的场景：

DELETE je
FROM jobEntries je
LEFT JOIN (
    SELECT jobId, MAX(createdAt) AS latest_created
    FROM jobEntries
    GROUP BY jobId
) latest ON je.jobId = latest.jobId AND je.createdAt = latest.latest_created
WHERE latest.jobId IS NULL;

方案2：用窗口函数确保只留一条（推荐）

如果存在同一jobId下多条记录createdAt相同的情况，用ROW_NUMBER()可以强制只保留一条（序号为1的那条）：

DELETE FROM jobEntries
WHERE id NOT IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY jobId ORDER BY createdAt DESC) AS rn
        FROM jobEntries
    ) t
    WHERE rn = 1
);

注意：MySQL不允许在DELETE的子查询中直接引用目标表，所以需要套一层子查询包裹窗口函数结果。

PostgreSQL 实现 #

方案1：子查询筛选待删除记录

DELETE FROM jobEntries
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY jobId ORDER BY createdAt DESC) AS rn
        FROM jobEntries
    ) t
    WHERE rn > 1
);

方案2：用CTE（公共表表达式）更直观

WITH to_delete AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY jobId ORDER BY createdAt DESC) AS rn
    FROM jobEntries
)
DELETE FROM jobEntries
USING to_delete
WHERE jobEntries.id = to_delete.id AND to_delete.rn > 1;

SQL Server 实现 #

用CTE结合窗口函数即可：

WITH ranked_entries AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY jobId ORDER BY createdAt DESC) AS rn
    FROM jobEntries
)
DELETE FROM ranked_entries
WHERE rn > 1;

重要注意事项 #

1. 先验证再删除：执行DELETE前，建议把DELETE语句替换成SELECT，确认要删除的记录是正确的。比如把第一个MySQL方案中的DELETE je换成SELECT je.*，查看待删除数据。
2. 备份数据：操作删除前务必备份jobEntries表，避免误删数据。
3. 主键依赖：上述方案假设jobEntries表有唯一主键（比如id），如果没有主键，可以用(jobId, createdAt, 其他唯一字段)的组合来定位记录，或者调整窗口函数的排序逻辑确保唯一性。

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