讨论一下，关于取时间交集的有关问题（刚才楼主结贴了）

讨论一下，关于取时间交集的问题（刚才楼主结贴了）
是不是我考虑复杂了？
原帖如下：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)<=@edate) 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'
[解决办法]

方法，挺多！ 
 

  '2009-1-1' between BeginTime and EndTime  
 or '2009-10-1' between BeginTime and EndTime  

[解决办法]
      时间1                       时间2
1.starttime |       endtime                |
2.starttime |      |       endtime
3.               |        starttime   endtime         |
4.               |        starttime                   |       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'  and EndTime between '2009-1-1' and '2009-10-1')--2009-01-05 00:00:00.000    2009-11-01 00:00:00.000--2009-01-05 00:00:00.000    2009-09-01 00:00:00.000--2000-01-05 00:00:00.000    2009-11-01 00:00:00.000--2000-01-05 00:00:00.000    2009-08-01 00:00:00.000如果交集的话有三种情况（假设ENDTIME>=BEGINTIME）1. '2009-1-1' between BeginTime and EndTime 2. '2009-10-1' between BeginTime and EndTime3. BeginTime and EndTime in '2009-1-1' and '2009-10-1'区间
[解决办法]
declare @sdate datetime
declare @edate datetime
set @sdate = '2009-1-1'
set @edate = '2009-10-1'

select m.* from tb m ,  
(
select  
   dateadd(dd,num,@sdate) dt
from  
   (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
   dateadd(dd,num,@sdate)<=@edate
) n
where n.dt between m.BeginTime and m.EndTime
[解决办法]
        |---------------|            统计时间段范围(20090101-20091001)

  ①  |---------|
  ②         |-----------------|
  ③   |---------------------------|
  ④          |-------|  
然后分析时间的时间范围与给定的统计时间段可能出现交集的情况（边界范围未考虑）
可以的得到上面四种情况。
根据该表，ＳＱＬ也就不能写了。
１２４得到实际的范围在统计范围之间满足。
３得到统计范围在实际范围中也满足。
得解～～～
[解决办法]
SQL code
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 行）*/
[解决办法]
探讨

是不是我考虑复杂了？