SQL 查询删除发生在 3000 毫秒内的记录

Question

我有一个记录某些用户事件的数据表。结果类似于:

ID    Username EventDate
1     UserA    2010-10-21 16:59:59.367
2     UserA    2010-10-21 17:00:00.114    
3     UserA    2010-10-21 17:00:00.003
4     UserA    2010-10-21 17:00:02.867
5     UserB    2010-10-21 18:43:26.538
6     UserB    2010-10-21 18:47:33.373

我想运行一个查询来删除所有发生的事件 在前一个事件的 3000 毫秒内 .请注意，毫秒是相关的。

结果表将如下所示:

ID    Username EventDate
1     UserA    2010-10-21 16:59:59.367
4     UserA    2010-10-21 17:00:02.867
5     UserB    2010-10-21 18:43:26.538
6     UserB    2010-10-21 18:47:33.373

我怎样才能做到这一点？

Answer 1

您可以使用 while循环一次删除一行。这样就避免了多行都在 3 秒内，但不在第一行 3 秒内的问题。

例如:

declare @t table (ID int, Username varchar(50), EventDate datetime)
insert @t
          select 1,     'UserA',    '2010-10-21 16:59:59.367'
union all select 2,     'UserA',    '2010-10-21 17:00:00.114'    
union all select 3,     'UserA',    '2010-10-21 17:00:00.003'
union all select 4,     'UserA',    '2010-10-21 17:00:02.867'
union all select 5,     'UserB',    '2010-10-21 18:43:26.538'
union all select 6,     'UserB',    '2010-10-21 18:47:33.373'


while 1=1
    begin
    delete  @t
    where   ID =
            (
            select  top 1 t2.ID
            from    @t t2
            where   exists
                    (
                    select  *
                    from    @t t1
                    where   t1.Username = t2.Username
                            and t1.EventDate < t2.EventDate
                            and datediff(millisecond, t1.EventDate, 
                                         t2.EventDate) <= 3000
                    )
            )

    if @@ROWCOUNT = 0 
        break
    end

select * from @t

这打印:

ID  Username    EventDate
1   UserA       2010-10-21 16:59:59.367
4   UserA       2010-10-21 17:00:02.867
5   UserB       2010-10-21 18:43:26.537
6   UserB       2010-10-21 18:47:33.373