如何选择或删除SQL重复数据行且保留其中一条记录？

需求说明 #

无需清空全表数据，仅删除SQL表内多余的重复数据行，每组重复项仅保留1条即可，具体示例如下：
原始表数据：

username ip_address

a 127.0.0.1
b 127.0.0.2
c 127.0.0.3
d 127.0.0.4 < 重复项 保留
d 127.0.0.4 < 重复项 需删除
e 127.0.0.5
d 127.0.0.4 < 重复项 需删除

需要筛选/删除的目标重复数据：

username ip_address

d 127.0.0.4
d 127.0.0.4

实现方案 #

方案1：临时表法（兼容性强，适配绝大多数数据库） #

操作步骤如下：

• 创建临时表存储原表去重后的数据

CREATE TABLE temp_table AS SELECT DISTINCT * FROM 你的表名;

• 清空原表数据

TRUNCATE TABLE 你的表名;

• 将临时表中去重后的数据回写到原表

INSERT INTO 你的表名 SELECT * FROM temp_table;

• 清理临时表

DROP TABLE temp_table;

方案2：窗口函数法（无需临时表，适配支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

DELETE t1 FROM 你的表名 t1
JOIN (
    SELECT 
        主键字段,
        ROW_NUMBER() OVER (PARTITION BY username, ip_address ORDER BY (SELECT NULL)) AS row_num
    FROM 你的表名
) t2 ON t1.主键字段 = t2.主键字段
WHERE t2.row_num > 1;

注：将语句中你的表名、主键字段替换为实际的表名、表主键字段名即可，PARTITION BY后可按实际判断重复的字段调整

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