执行与另一个表的联接时查找不存在的时间间隔

ua4mk5z4 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(214)

我有一个问题，我不知道是否有解决方案在sql中。举个小例子

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Events](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventTime] [datetime] NOT NULL,
    [Event] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
/******Object:  Table [dbo].[Logging]    Script Date: 2020-07-07 14:35:17******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Logging](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FromTime] [datetime] NOT NULL,
    [ToTime] [datetime] NOT NULL,
    [Status] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Events] ON 
GO
INSERT [dbo].[Events] ([Id], [EventTime], [Event]) VALUES (1, CAST(N'2020-07-07T14:14:00.000' AS DateTime), N'str')
GO
INSERT [dbo].[Events] ([Id], [EventTime], [Event]) VALUES (2, CAST(N'2020-07-07T07:01:00.000' AS DateTime), N'testcall')
GO
INSERT [dbo].[Events] ([Id], [EventTime], [Event]) VALUES (3, CAST(N'2020-07-07T15:22:00.000' AS DateTime), N'ipfail')
GO
SET IDENTITY_INSERT [dbo].[Events] OFF
GO
SET IDENTITY_INSERT [dbo].[Logging] ON 
GO
INSERT [dbo].[Logging] ([Id], [FromTime], [ToTime], [Status]) VALUES (1, CAST(N'2020-07-07T01:00:00.000' AS DateTime), CAST(N'2020-07-07T13:00:00.000' AS DateTime), N'All well')
GO
INSERT [dbo].[Logging] ([Id], [FromTime], [ToTime], [Status]) VALUES (2, CAST(N'2020-07-07T13:01:00.000' AS DateTime), CAST(N'2020-07-07T15:00:00.000' AS DateTime), N'All well')
GO
INSERT [dbo].[Logging] ([Id], [FromTime], [ToTime], [Status]) VALUES (3, CAST(N'2020-07-07T15:33:00.000' AS DateTime), CAST(N'2020-07-07T20:00:00.000' AS DateTime), N'All well')
GO
INSERT [dbo].[Logging] ([Id], [FromTime], [ToTime], [Status]) VALUES (4, CAST(N'2020-07-07T20:01:00.000' AS DateTime), CAST(N'2020-07-07T23:00:00.000' AS DateTime), N'All well')
GO
SET IDENTITY_INSERT [dbo].[Logging] OFF
GO

在执行此表的内部联接时，我希望在事件表中查找日志中没有匹配时间段的事件。缺少的时间段是15:00到15:33，发生的事件是id为3的事件。一切都是以分钟来衡量的。这可能吗？或者用别的方法？

sql DateTime Between tsql

来源：https://stackoverflow.com/questions/62775585/find-non-existing-time-intervall-when-doing-join-to-another-table

1条答案

按热度按时间

fhg3lkii1#

如果我理解正确，你想 not exists :

select l.*
from logging l
where not exists (select 1
                  from events e
                  where e.eventtime >= l.fromtime and
                        e.eventtime <= l.totime
                 );

但是，这会返回两个时间段——一个ID为3和4的时间段。
这是一把小提琴。

赞(0）回复(0）举报 2021-07-24

我来回答

执行与另一个表的联接时查找不存在的时间间隔

1条答案

相关问题

热门标签

最新问答