如何修改SQL存储过程删除同员工同日期下时间差小于指定值的重复行（保留首条）

原有代码的核心缺陷 #

• 窗口函数分组规则把精确time字段纳入PARTITION BY，仅能匹配时间完全一致的重复行，完全没有调用阈值参数@DupTime
• 没有建立“保留记录”的比较锚点，仅靠相邻原始行的时间差做判断，遇到连续多段短间隔打卡时会出现误判
• 过滤条件中的ClockDate字段和表结构不匹配，实际表中日期字段为date，执行时会触发字段不存在的报错

正确判定规则落地 #

判定逻辑完全匹配需求：

1. 按同empnum、同date维度，将所有记录按ID升序排序（对应打卡先后顺序）
2. 逐行锚定实际保留的最早一条记录作为比较基准，计算当前行与基准的时间差
3. 若时间差小于/等于@DupTime分钟，标记为重复行待删除；若时间差超过阈值，将当前行设为新的比较基准
4. 删除所有标记为重复的记录

对照示例阈值2分钟的场景验证：

• emp1的08:01与基准08:00差1分钟，判重删除
• emp1的16:32与基准16:30差2分钟，判重删除
• emp2的17:03与基准17:00差3分钟，超过阈值，正常保留

可直接使用的修正版存储过程 #

CREATE OR ALTER PROCEDURE [dbo].[DelDup]
    @fDate date
AS 
BEGIN
    -- 获取去重时间阈值，取单值避免多行查询报错
    DECLARE @DupTime INT = (SELECT TOP 1 [DupTime] FROM [dbo].[ProgramOptions] ORDER BY 1);

    -- 递归CTE实现保留锚点逻辑
    WITH DupAnchor AS (
        -- 锚点初始化：每个同empnum、同date组内ID最小的记录为第一条保留锚点
        SELECT 
            ID,
            [date],
            [time],
            empnum,
            CAST([time] AS datetime) AS anchor_time,
            0 AS is_deleted -- 0=保留，1=删除
        FROM clockdates
        WHERE ID IN (
            SELECT MIN(ID) 
            FROM clockdates
            WHERE [date] >= @fDate
            GROUP BY empnum, [date]
        )

        UNION ALL

        -- 逐行匹配同组下一条记录，和当前锚点比较时间差
        SELECT 
            cur.ID,
            cur.[date],
            cur.[time],
            cur.empnum,
            -- 时间差超过阈值则更新为新的锚点，否则沿用旧锚点
            CASE 
                WHEN DATEDIFF(MINUTE, anchor_time, CAST(cur.[time] AS datetime)) > @DupTime 
                THEN CAST(cur.[time] AS datetime)
                ELSE anchor_time
            END AS anchor_time,
            -- 时间差小于等于阈值则标记为删除
            CASE 
                WHEN DATEDIFF(MINUTE, anchor_time, CAST(cur.[time] AS datetime)) <= @DupTime 
                THEN 1 
                ELSE 0 
            END AS is_deleted
        FROM clockdates cur
        INNER JOIN DupAnchor a 
            ON cur.empnum = a.empnum 
            AND cur.[date] = a.[date]
            AND cur.ID > a.ID -- 只找同组中比当前锚点ID更大的未处理记录
    )
    -- 删除所有标记为待删除的重复行
    DELETE del
    FROM clockdates del
    INNER JOIN DupAnchor a 
        ON del.ID = a.ID
    WHERE a.is_deleted = 1
OPTION (MAXRECURSION 1000); -- 调整递归层级适配单组最多1000条记录的场景
END

代码说明 #

• 用CAST([time] AS datetime)完成time类型的时间计算，避免跨零点的时间差计算异常
• 保留了原存储过程的@fDate起始日期过滤条件，不会处理早于指定日期的历史数据
• 阈值判断逻辑完全匹配示例的预期输出：2分钟阈值下，ID为2、5的记录会被删除，其余记录保留

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