如何使用SQL中的RANK()函数删除重复行？

我有一张包含name、id、email三列的表，其中id存在重复值需要清理，尝试用RANK()函数编写删除语句，现有如下查询：

delete 
    d
from 
    duplicates d
    inner join (
 select name,
        RANK() OVER (PARTITION BY Id ORDER BY Id DESC) AS RowNumberWithinDuplicateSet
 from duplicates
  ) x on d.id = x.id



delete 
    d
from 
    duplicates d
    inner join (
 select Id,
        RANK() OVER (PARTITION BY CustomerId, OrderAmount ORDER BY Id DESC) AS RowNumberWithinDuplicateSet
 from duplicates
 where 
            OrderDate = '4/20/2020'
 ) x on d.id = x.id

现有语句的问题 #

1. 第一个语句：

   • PARTITION BY Id 将相同id的行归为一组，但ORDER BY Id DESC后，同组内所有行的排名都是1（因为id完全相同），执行删除会把所有重复id的行全部删掉，无法保留一行。
   • 子查询选择name无实际作用，且缺少筛选排名大于1的条件，逻辑完全错误。

2. 第二个语句：

   • 原表仅包含name、id、email，但语句中用到CustomerId、OrderAmount、OrderDate，列名不匹配，直接执行会报错。
   • 同样存在RANK()并列排名的问题，且未添加筛选条件，无法实现保留唯一行的需求。

修正后的删除语句 #

针对id重复的清理（保留每组id的一行）

推荐用ROW_NUMBER()替代RANK()，它会给同组内每行分配唯一序号，避免并列导致的误删：

DELETE d
FROM duplicates d
INNER JOIN (
    SELECT 
        id,
        -- 按id降序排序，保留最大id对应的行；若要保留最早行，改为ORDER BY Id ASC
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) AS RowNum
    FROM duplicates
) x ON d.id = x.id
WHERE x.RowNum > 1;

如果坚持使用RANK()，需要加入能区分行的辅助排序字段（比如email），并添加筛选条件：

DELETE d
FROM duplicates d
INNER JOIN (
    SELECT 
        id,
        email,
        RANK() OVER (PARTITION BY id ORDER BY email DESC) AS RowNumberWithinDuplicateSet
    FROM duplicates
) x ON d.id = x.id AND d.email = x.email
WHERE x.RowNumberWithinDuplicateSet > 1;

若为其他表的重复行清理（对应第二个语句的需求）

如果是另一张包含CustomerId、OrderAmount、OrderDate的表，要删除指定日期下组合字段的重复行，修正后语句如下：

DELETE d
FROM duplicates d
INNER JOIN (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY CustomerId, OrderAmount ORDER BY Id DESC) AS RowNum
    FROM duplicates
    WHERE OrderDate = '2020-04-20' -- 使用标准ISO日期格式避免歧义
) x ON d.id = x.id
WHERE x.RowNum > 1;

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