如何编写SQL Delete查询删除重复ID的冗余记录并保留最新日期条目

要实现保留每个ID最新日期记录、删除冗余重复项的需求，这里提供几种不同数据库兼容的SQL写法，你可以根据自己使用的数据库选择合适的方案：

方案1：通用子查询方式（兼容多数数据库） #

这个方法先通过子查询找出每个ID对应的最新日期，再删除所有不匹配这些（ID+最新日期）组合的记录。部分数据库（比如MySQL）不允许DELETE语句直接引用同表子查询，所以可以用JOIN的方式规避：

兼容MySQL的写法： #

DELETE t1
FROM your_table t1
LEFT JOIN (
    -- 先获取每个ID的最新日期
    SELECT id, MAX(date) AS max_date
    FROM your_table
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.date = t2.max_date
-- 左连接后，没有匹配到最新日期的记录就是要删除的冗余项
WHERE t2.id IS NULL;

通用子查询写法（适用于PostgreSQL、SQL Server等）： #

DELETE FROM your_table
WHERE (id, date) NOT IN (
    SELECT id, MAX(date)
    FROM your_table
    GROUP BY id
);

方案2：窗口函数写法（适用于支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server等） #

这种方式更直观，通过ROW_NUMBER()窗口函数给每个ID下的记录按日期倒序排名，排名大于1的就是冗余记录，直接删除即可：

WITH ranked_records AS (
    SELECT 
        *,
        -- 按ID分组，日期倒序排序，最新的记录排名为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS record_rank
    FROM your_table
)
DELETE FROM ranked_records
WHERE record_rank > 1;

重要提醒 #

1. 请将上述代码中的your_table替换为你的实际表名
2. 执行DELETE前，建议先用SELECT语句验证要删除的记录是否正确，比如：

-- 验证要删除的记录
SELECT *
FROM your_table
WHERE (id, date) NOT IN (
    SELECT id, MAX(date)
    FROM your_table
    GROUP BY id
);

这样可以避免误删数据。

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