整个日期的 SQL 差异

发布于 2024-11-05 08:07:11 字数 355 浏览 0 评论 0原文

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

ID | sectionID | eventDateTime | ...

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

原文

I am trying to devise a query that will group records by date. I have records that represent events that have occurred. If events occurred on the same section and within 50ms of each other, they should be grouped. The important part of my table looks like this:

ID | sectionID | eventDateTime | ...

I am thinking I would want to group by sectionID and eventDateTime (a sectionID will span IDs) and use a having clause to define my time grouping constraints. I also think this might not be possible with a query. I saw DATEDIFF(), but that only does calculations on a single component of the dates. Any help would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蘸点软妹酱 2024-11-12 08:07:11

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

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

Are you looking for something akin to the following:

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

回复收藏 0 原文

~没有更多了~