当前位置: 代码迷 >> Sql Server >> 讨论一下,关于取时间交集的有关问题(刚才楼主结贴了)
  详细解决方案

讨论一下,关于取时间交集的有关问题(刚才楼主结贴了)

热度:289   发布时间:2016-04-27 17:58:23.0
讨论一下,关于取时间交集的问题(刚才楼主结贴了)
是不是我考虑复杂了?
原帖如下:http://topic.csdn.net/u/20091019/09/f2b7d5c2-bb97-44bb-9d52-b1d734abd007.html?85143


SQL code
create table #t(BeginTime datetime,EndTime datetime)insert #t select'2009-01-05','2009-11-01' union all select  -- '2009-10-1' between BeginTime and EndTime '2009-01-05','2009-09-01' union all select  -- '2009-1-1' between BeginTime and EndTime'2000-01-05','2009-11-01' union all select  -- '2009-1-1' between BeginTime and EndTime      '2009-10-1' between BeginTime and EndTime '2009-11-05','2009-11-10' union all select  -- '2009-01-05','2009-08-01'             -- BeginTime between '2009-1-1' and '2009-10-1'   EndTime between '2009-1-1' and '2009-10-1'                      select * from #twhere '2009-1-1' between BeginTime and EndTime   or '2009-10-1' between BeginTime and EndTime /*BeginTime                                              EndTime                                                ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000                                2009-11-01 00:00:00.0002000-01-05 00:00:00.000                                2009-11-01 00:00:00.000(所影响的行数为 2 行)*/select * from #twhere  BeginTime between '2009-1-1' and '2009-10-1'    or EndTime between '2009-1-1' and '2009-10-1'/*BeginTime                                              EndTime                                                ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000                                2009-11-01 00:00:00.0002009-01-05 00:00:00.000                                2009-09-01 00:00:00.0002009-01-05 00:00:00.000                                2009-08-01 00:00:00.000(所影响的行数为 3 行)*/select * from #twhere '2009-1-1' between BeginTime and EndTime   or '2009-10-1' between BeginTime and EndTime   or BeginTime between '2009-1-1' and '2009-10-1'  or EndTime between '2009-1-1' and '2009-10-1'/*BeginTime                                              EndTime                                                ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000                                2009-11-01 00:00:00.0002009-01-05 00:00:00.000                                2009-09-01 00:00:00.0002000-01-05 00:00:00.000                                2009-11-01 00:00:00.0002009-01-05 00:00:00.000                                2009-08-01 00:00:00.000(所影响的行数为 4 行)*/drop table #t


------解决方案--------------------
SQL code
declare @sdate datetimedeclare @edate datetimeset @sdate = '2009-1-1'set @edate = '2009-10-1'select m.* from tb m , (select     dateadd(dd,num,@sdate) dtfrom     (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere    dateadd(dd,num,@sdate)<[email protected]) nwhere n.dt between m.BeginTime and m.EndTime
------解决方案--------------------
如果是说BeginTime and EndTime 的时间在'2009-1-1' and '2009-10-1' 之内.

select * from #t
where '2009-1-1' between BeginTime and EndTime 
or '2009-10-1' between BeginTime and EndTime 

------解决方案--------------------
觉得这个准确点。
SQL code
select * from #twhere '2009-1-1' between BeginTime and EndTime   or '2009-10-1' between BeginTime and EndTime   or BeginTime between '2009-1-1' and '2009-10-1'  or EndTime between '2009-1-1' and '2009-10-1'
------解决方案--------------------

方法,挺多!