数据查询之参数区段落入数据区段查询

最新推荐文章于 2023-08-03 09:28:34 发布

周道

最新推荐文章于 2023-08-03 09:28:34 发布

阅读量296

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/lemon627497658/article/details/68117019

版权

有一个场景，某一事件发生时间为7:00，结束时间为8:10。现在输入参数开始时间为7:00，结束时间为8:00，要求该条数据也能显示。

这时普通的开始时间大于等于参数开始时间、结束时间小于等于参数结束时间已不能满足要求。

解决方法为：开始时间小于等于参数结束时间、结束时间大于等于参数开始时间。


CREATE TABLE [dbo].[RANGE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[START_DATE] [date] NOT NULL,
	[END_DATE] [date] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[RANGE] ON 

INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (17, CAST(N'2010-01-01' AS Date), CAST(N'2010-01-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (18, CAST(N'2010-02-01' AS Date), CAST(N'2010-02-28' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (19, CAST(N'2010-03-01' AS Date), CAST(N'2010-03-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (20, CAST(N'2010-04-01' AS Date), CAST(N'2010-04-30' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (21, CAST(N'2010-05-01' AS Date), CAST(N'2010-05-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (22, CAST(N'2010-06-01' AS Date), CAST(N'2010-06-30' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (23, CAST(N'2010-07-01' AS Date), CAST(N'2010-07-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (24, CAST(N'2010-08-01' AS Date), CAST(N'2010-08-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (25, CAST(N'2010-09-01' AS Date), CAST(N'2010-09-30' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (26, CAST(N'2010-10-01' AS Date), CAST(N'2010-10-31' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (27, CAST(N'2010-11-01' AS Date), CAST(N'2010-11-30' AS Date))
INSERT [dbo].[RANGE] ([ID], [START_DATE], [END_DATE]) VALUES (28, CAST(N'2010-12-01' AS Date), CAST(N'2010-12-31' AS Date))
SET IDENTITY_INSERT [dbo].[RANGE] OFF

例如现在要求查询出从 2010-01-02 到 2010-05-30 的数据

写法：

SELECT TOP (1000) [ID]
,[START_DATE]
,[END_DATE]
FROM [dbo].[RANGE] A
WHERE A.START_DATE <= '2010-05-30'
AND A.END_DATE >= '2010-01-02'

逻辑：可将时间分为三段 2010-01-02之前 2010-01-02 ----2010-05-30 2010-05-30 之后

希望事件时间段落在2010-01-02 ----2010-05-30 的反面即是（事件结束时间小于 2010-01-02 或者事件开始时间大于2010-05-30）

则 NOT(事件结束时间小于 2010-01-02 或者事件开始时间大于2010-05-30) 即为事件时间段落在2010-01-02 ----2010-05-30

即事件结束时间大于等于2010-01-02 AND 事件开始时间小于等于2010-05-30

备注：这种写法有一个问题，即输入的开始时间参数可能会大于结束时间参数。还需在语句后面加上： AND 开始时间参数<= 结束时间参数。