删除Test表中同EventID、VenueID且同日时差<2小时的冗余行

现有Test表数据 #

id  eventid startdate           venueid
1   32  2023-03-16 19:30:00.000 3213934
2   32  2023-03-16 20:00:00.000 3213934
3   32  2023-03-16 20:30:00.000 3213934
4   32  2023-03-16 21:31:00.000 3213934

需求说明 #

删除表中满足以下条件的行：

• 属于同一eventid和venueid
• 处于同一天内
• 与同组内其他行的时间差小于2小时
  最终保留id为1和4的两行（二者时差超过2小时，id2、3与id1时差均小于2小时）

原查询问题分析 #

你之前的查询仅筛选出了存在短时间差关联的记录，但无法直接用于删除操作；另外使用datediff(hh)判断小时差存在逻辑漏洞——该函数仅计算小时数的整数差，忽略分钟数，比如19:30到21:29的实际时差为119分钟（小于2小时），但datediff(hh)会返回2，导致误判。

正确SQL解决方案 #

方法1：CTE+窗口函数（推荐） #

通过窗口函数LAG()获取同组内上一条记录的时间，标记出与上一条记录时差小于2小时的行并删除：

WITH ranked_data AS (
    SELECT 
        id,
        eventid,
        startdate,
        venueid,
        -- 按eventid、venueid、日期分组，按时间排序，取上一条记录的时间
        LAG(startdate) OVER (
            PARTITION BY eventid, venueid, CONVERT(date, startdate)
            ORDER BY startdate
        ) AS prev_startdate
    FROM Test
)
DELETE FROM ranked_data
WHERE DATEDIFF(MINUTE, prev_startdate, startdate) < 120; -- 120分钟=2小时

执行后会删除id为2、3的记录，保留目标行。

方法2：关联删除 #

通过自关联找到所有符合条件的待删除记录，限制只删除时间晚于关联记录的行，避免循环删除：

DELETE t1
FROM Test t1
JOIN Test t2 
    ON t1.eventid = t2.eventid
    AND t1.venueid = t2.venueid
    AND CONVERT(date, t1.startdate) = CONVERT(date, t2.startdate)
    AND t1.startdate > t2.startdate -- 仅删除时间更晚的重复行
    AND DATEDIFF(MINUTE, t2.startdate, t1.startdate) < 120;

该方法会删除同组同一天内，与更早记录时差小于2小时的行，最终保留id1和4。

关键注意点 #

• 必须用分钟数（DATEDIFF(MINUTE)）判断时间差，避免datediff(hh)的整数小时差误判问题。
• 分组时要包含eventid、venueid和日期，确保仅在同一组同一天内进行时间比较。

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