排序后基于File No_与Line No_列删除SQL重复行

要实现按指定规则排序后，仅保留每个[File No_]和[Line No_]组合的第一行、删除其余重复行，可以用窗口函数ROW_NUMBER()标记行号，再筛选删除重复项，具体方案如下：

1. 先验证要保留的行（推荐先执行此步骤确认结果） #

用SELECT语句先查看最终要保留的行是否符合预期：

SELECT 
    [File No_], 
    [Line No_], 
    [Created on]
FROM (
    SELECT 
        *,
        -- 按[File No_]+[Line No_]分组，组内按指定规则排序并编号
        ROW_NUMBER() OVER (
            PARTITION BY [File No_], [Line No_] 
            ORDER BY [File No_], [Line No_] DESC, [Created on] DESC
        ) AS RowNum
    FROM dbo.[Shipment Serv_ Fin_ Status]
    WHERE [File No_] = N'TA-P200081258' -- 当前筛选单个File No_，后续可移除
) t
WHERE RowNum = 1 -- 只保留每个组的第一行
ORDER BY [File No_], [Line No_] DESC, [Created on] DESC;

2. 执行删除操作 #

确认结果正确后，执行以下语句删除重复行：

针对单个File No_的删除 #

WITH RankedRows AS (
    SELECT 
        [File No_], 
        [Line No_], 
        [Created on],
        ROW_NUMBER() OVER (
            PARTITION BY [File No_], [Line No_] 
            ORDER BY [File No_], [Line No_] DESC, [Created on] DESC
        ) AS RowNum
    FROM dbo.[Shipment Serv_ Fin_ Status]
    WHERE [File No_] = N'TA-P200081258'
)
DELETE FROM RankedRows
WHERE RowNum > 1; -- 删除组内编号大于1的重复行

移除筛选条件后，处理全表的删除 #

如果后续要去掉File No_的筛选，直接删除WHERE子句即可：

WITH RankedRows AS (
    SELECT 
        [File No_], 
        [Line No_], 
        [Created on],
        ROW_NUMBER() OVER (
            PARTITION BY [File No_], [Line No_] 
            ORDER BY [File No_], [Line No_] DESC, [Created on] DESC
        ) AS RowNum
    FROM dbo.[Shipment Serv_ Fin_ Status]
)
DELETE FROM RankedRows
WHERE RowNum > 1;

关键逻辑说明 #

• PARTITION BY [File No_], [Line No_]：将数据按这两列的组合分组，每个组视为一组重复项
• ORDER BY：按你指定的规则对组内行排序，确保编号为1的行是你想保留的目标行
• RowNum > 1：标记为这些编号的行是组内的重复项，执行删除即可

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