完整日期的SQL差异

Question

我正在尝试设计一个按日期对记录进行分组的查询。我有代表已发生事件的记录。如果事件发生在同一区段且彼此相隔不超过50ms，则应对它们进行分组。我的表格的重要部分如下所示：

ID | sectionID | eventDateTime | ...

我在考虑按sectionID和eventDateTime进行分组( sectionID将跨越多个ID)，并使用having子句来定义时间分组约束。我也认为这对于查询来说可能是不可能的。我看到了DATEDIFF()，但它只对日期的一个组成部分进行计算。任何帮助都将不胜感激。

Answer 1

您是否在寻找类似于以下内容的内容：

With [Events] As
    (
    Select 1 As Id, 5 As SectionId, Cast('2011-05-04 23:59:59.950' As DateTime) As EventDateTime
    Union All Select 2, 5, '2011-05-05 00:00:00.000'
    Union All Select 3, 5, '2011-05-05 00:00:00.049'
    )
    , TaggedData As
    (
    Select E1.Id, E1.sectionID
        , Cast(Coalesce(E2.EventDateTime, E1.EventDateTime) As Date) As TagEventDateTime
    From [Events] As E1
        Left Join [Events] As E2
            ON E2.sectionID = E1.sectionID
                And E2.Id <> E1.Id
                And E2.EventDateTime >= DateAdd(ms, -50, E1.EventDateTime)
                And E2.EventDateTime <= E1.EventDateTime
    )
Select SectionId, TagEventDateTime
From TaggedData
Group By SectionId, TagEventDateTime