无唯一列的SQL表如何删除重复行并仅保留其中一条记录

我有一张存在重复条目的SQL表，需要删除重复项、仅保留每组重复条目中的一条。这些重复条目所有列的内容完全一致，没有可用于区分彼此的字段：

我使用如下查询统计了重复数据的数量：

select url_rewrite_id, category_id, product_id, count(*) cnt
from catalog_url_rewrite_product_category
group by url_rewrite_id, category_id, product_id
having cnt > 1
order by cnt desc

我尝试使用该查询的变体删除所有重复项：

delete
from catalog_url_rewrite_product_category
where url_rewrite_id in (
    select url_rewrite_id
    from catalog_url_rewrite_product_category
    group by url_rewrite_id, category_id, product_id
    having count(*) > 1
)

但该方案会删除所有符合条件的重复条目，无法保留其中一条。同类常见方案都默认表存在唯一id类字段，不符合当前数据结构场景。

针对全字段完全重复、无唯一标识字段的去重场景，可根据你使用的数据库类型选择以下两种方案：

方案1：全数据库通用临时表方案 #

该方案兼容所有SQL数据库，逻辑简单不易出错，适合各类场景：

1. 先将原表去重后的所有数据存入临时表

CREATE TEMPORARY TABLE temp_distinct_data AS
SELECT DISTINCT * FROM catalog_url_rewrite_product_category;

2. 清空原表所有数据

TRUNCATE TABLE catalog_url_rewrite_product_category;

3. 将临时表中去重后的数据插回原表

INSERT INTO catalog_url_rewrite_product_category
SELECT * FROM temp_distinct_data;

临时表仅在当前数据库会话生效，操作完成后会自动销毁，不会残留冗余数据。如果数据量极大，该方案执行效率也远高于逐行删除。

方案2：支持窗口函数的数据库方案（MySQL 8.0+/PostgreSQL/SQL Server等） #

如果你的数据库支持窗口函数，可以直接用CTE+行号过滤的方式原地删除重复项，不需要临时中转表：

WITH duplicate_mark AS (
    SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY url_rewrite_id, category_id, product_id 
        ORDER BY (SELECT NULL)
    ) AS row_num
    FROM catalog_url_rewrite_product_category
)
DELETE FROM duplicate_mark WHERE row_num > 1;

其中ORDER BY (SELECT NULL)是因为不需要指定排序规则，只要给同一重复组的每条记录分配不同行号即可。

• 操作前务必对原表做全量备份，避免误操作导致数据丢失
• 建议先在测试环境验证方案可行性后再到生产环境执行
• 数据量大的场景建议在业务低峰期操作，避免锁表影响正常业务

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