如何在SQL中删除重复用户记录，保留最新created_at条目

需求说明 #

针对包含重复user_id的users表，删除重复条目，仅保留每个user_id对应created_at时间戳最新的记录，要求操作安全高效，支持PostgreSQL/MySQL/SQL Server，适配大型数据集。

前置安全操作（必做） #

在执行删除前，务必确认待删除数据，避免误删：

1. 备份数据表

   -- PostgreSQL/MySQL
   CREATE TABLE users_backup AS SELECT * FROM users;
   
   -- SQL Server
   SELECT * INTO users_backup FROM users;
   

2. 查询待删除的重复记录

   SELECT *
   FROM (
       SELECT *,
              ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
       FROM users
   ) t
   WHERE rn > 1;
   

   确认结果符合预期后，再执行删除。

各数据库删除方案 #

PostgreSQL

使用CTE（公共表表达式）实现清晰的删除逻辑，适合大数据集：

WITH duplicate_records AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM users
USING duplicate_records
WHERE users.id = duplicate_records.id AND duplicate_records.rn > 1;

性能优化：创建索引避免全表扫描

CREATE INDEX idx_users_userid_createdat ON users(user_id, created_at DESC);

MySQL

MySQL 8.0+支持窗口函数，优先使用以下方案：

DELETE u
FROM users u
JOIN (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM users
) t ON u.id = t.id
WHERE t.rn > 1;

若使用MySQL 5.x（无窗口函数），可使用关联删除：

DELETE u1
FROM users u1
JOIN users u2 ON u1.user_id = u2.user_id AND u1.created_at < u2.created_at;

性能优化：创建索引提升关联效率

CREATE INDEX idx_users_userid_createdat ON users(user_id, created_at);

SQL Server

支持CTE直接删除，逻辑简洁：

WITH duplicate_records AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM duplicate_records
WHERE rn > 1;

性能优化：创建包含主键的非聚集索引

CREATE NONCLUSTERED INDEX idx_users_userid_createdat ON users(user_id, created_at DESC) INCLUDE (id);

关键注意事项 #

• 窗口函数（ROW_NUMBER()）方案逻辑清晰，仅扫描表一次，是大型数据集的最优选择
• 索引是性能保障的核心，没有合适的索引会导致全表扫描，处理大数据时性能极差
• 超大型表建议分批次删除（例如每次删除1000条），避免长时间锁表影响业务
• 执行删除后，可重新查询确认重复记录已清除

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