基于日期与条件的SQL重复数据删除技术问询

场景与数据准备 #

现有一张包含重复数据的临时表#StackOverFlow，表结构与插入数据的SQL如下：

CREATE TABLE #StackOverFlow
(
    [ctrc_num] int, 
    [Ctrc_name] varchar(6),
    [docu] bit, 
    [adj] bit, 
    new bit, 
    [some_date] datetime
);
    
INSERT INTO #StackOverFlow
    ([ctrc_num], [Ctrc_name], [docu], [adj], [new], [some_date])
VALUES
    (12345, 'John R', null, null, 1, '2023-12-11 09:05:13.003'),
    (12345, 'John R', 1, null, 0, '2023-12-11 09:05:12.987'),
    (12345, 'John R', null, null, 1, '2023-12-11 09:05:12.947'),
    (56789, 'Sam S', null, null, 1, '2023-12-11 09:05:13.003'),
    (56789, 'Sam S', null, null, 1, '2023-12-11 09:05:12.987'),
    (56789, 'Sam S', 1, null, 0, '2023-12-11 09:05:12.947'),
    (78945, 'Pat P', null, null, 1, '2023-12-11 09:05:13.003'),
    (78945, 'Pat P', null, null, 1, '2023-12-11 09:05:12.987'),
    (78945, 'Pat P', null, null, 1, '2023-12-11 09:05:12.947');

当前表中数据如下：

[ctrc_num]  [Ctrc_name] [docu]  [adj]   [new]   [some_date]
-----------------------------------------------------------------------
12345        John R     NULL    NULL    1       2023-12-11 09:05:13.003
12345        John R     1       NULL    0       2023-12-11 09:05:12.987
12345        John R     NULL    NULL    1       2023-12-11 09:05:12.947
56789        Sam S      NULL    NULL    1       2023-12-11 09:05:13.003
56789        Sam S      NULL    NULL    1       2023-12-11 09:05:12.987
56789        Sam S      1       NULL    0       2023-12-11 09:05:12.947
78945        Pat P      NULL    NULL    1       2023-12-11 09:05:13.003
78945        Pat P      NULL    NULL    1       2023-12-11 09:05:12.987
78945        Pat P      NULL    NULL    1       2023-12-11 09:05:12.947

清理规则 #

需按以下规则删除重复记录：

• 若同一ctrc_num、Ctrc_name分组下存在new=0的记录，删除所有new=1的记录
• 若同一ctrc_num、Ctrc_name分组下所有记录的new值均为1，仅保留some_date最新的一条记录，删除其余旧记录

预期清理后结果：

[ctrc_num]  [Ctrc_name] [docu]  [adj]  [new]    [some_date]
-----------------------------------------------------------------------
12345        John R     1       NULL    0       2023-12-11 09:05:12.987
56789        Sam S      1       NULL    0       2023-12-11 09:05:12.947
78945        Pat P      NULL    NULL    1       2023-12-11 09:05:13.003

已尝试的方法及不足 #

1. ROW_NUMBER函数：

;WITH RankedByDate AS
(
    SELECT 
        ctrc_num, Ctrc_name,
        docu, adj, new, some_date,
        ROW_NUMBER() OVER (PARTITION BY Ctrc_num, Ctrc_name, [docu],[adj], [new] 
                           ORDER BY some_date DESC) AS rNum
    FROM 
        #StackOverFlow
)
SELECT * 
FROM RankedByDate

仅能区分new=0的记录，但仍会保留排序后的new=1记录，无法满足删除所有new=1的需求。

2. GROUP BY分组：

SELECT [ctrc_num]
    ,[Ctrc_name]
    ,[docu]
    ,[adj]
    ,[new]
FROM 
    #StackOverFlow
GROUP BY 
    [ctrc_num]
    ,[Ctrc_name]
    ,[docu]
    ,[adj]
    ,[new]
HAVING 
    COUNT(*) > 1

仅能识别重复记录，但无法直接按规则删除目标记录。

正确解决方案 #

使用CTE结合窗口函数，先标记分组特征再执行删除：

;WITH CTE_Records AS (
    SELECT 
        *,
        -- 标记当前分组是否存在new=0的记录
        MAX(CASE WHEN new = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY ctrc_num, Ctrc_name) AS has_new0,
        -- 按日期倒序给分组内记录排名，用于保留最新记录
        ROW_NUMBER() OVER (PARTITION BY ctrc_num, Ctrc_name ORDER BY some_date DESC) AS rn
    FROM #StackOverFlow
)
DELETE FROM CTE_Records
WHERE 
    -- 分组存在new=0时，删除所有new=1的记录
    (has_new0 = 1 AND new = 1)
    -- 分组无new=0时，删除排名大于1的旧记录
    OR (has_new0 = 0 AND rn > 1);

-- 验证清理结果
SELECT * FROM #StackOverFlow;

逻辑说明 #

• 通过MAX(CASE...)窗口函数，判断每个ctrc_num+Ctrc_name分组内是否存在new=0的记录
• 通过ROW_NUMBER()窗口函数，给每个分组内的记录按some_date倒序排名，排名1的为最新记录
• 删除条件分两种场景：存在new=0时删所有new=1；不存在时删排名非1的旧记录，完全匹配需求规则

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