【C#】根据开始时间和结束时间筛选存在的信息

背景

业务需求中，需要根绝开始时间和结束时间筛选一段时间内的任务存在个数。

示例图片

根据开始时间 9：00到 结束时间11：00 筛选信息

总共有这么四种情况可能出现

插入测试数据

CREATE TABLE [dbo].[Schedule] ([ScheduleId] int NOT NULL IDENTITY(1,1) ,[ScheduleTheme] nvarchar(100) NOT NULL ,[ScheduleContent] nvarchar(500) NULL ,[ScheduleBeginTime] datetime NULL ,[ScheduleEndTime] datetime NULL ,[CreateTime] datetime  NULL)INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-04 10:00:00\', \'2020-07-08 11:00:00\', GETDATE());INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-06 10:00:00\', \'2020-07-09 11:00:00\', GETDATE());INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-04 10:00:00\', \'2020-07-06 11:00:00\', GETDATE());INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-06 10:00:00\', \'2020-07-06 11:00:00\', GETDATE());INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-01 10:00:00\', \'2020-07-04 11:00:00\', GETDATE());INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES (\'测试标题001\', \'\', \'2020-07-08 10:00:00\', \'2020-07-14 11:00:00\', GETDATE());

示例LINQ 代码

var beginDate=new DateTime(“2020-07-07”);var endDate=new DateTime(“2020-07-07”);var tasks = demo.Where(n =>(n.ScheduleBeginTime > endDate && n.ScheduleBeginTime > beginDate)||(n.ScheduleBeginTime > beginDate && n.ScheduleEndTime < endDate) ||(n.ScheduleEndTime < endDate && n.ScheduleEndTime > beginDate) ||(n.ScheduleEndTime > endDate && n.ScheduleBeginTime < beginDate);

sql 示例

SELECT * FROM Schedule WHERE 1!=1or (ScheduleBeginTime < \'2020-07-07\'  AND  ScheduleBeginTime > \'2020-07-05\')  --查询1种情况or (ScheduleBeginTime > \'2020-07-05\'  AND  ScheduleEndTime < \'2020-07-07\' )   --查询2种情况or (ScheduleEndTime < \'2020-07-07\'  AND  ScheduleEndTime > \'2020-07-05\')    --查询3种情况or (ScheduleEndTime > \'2020-07-07\'  AND  ScheduleBeginTime < \'2020-07-05\') --查询4种情况