日期:2014-05-18 浏览次数:20657 次
create table A(id int identity(1,1) not null primary key,start_time datetime,end_time datetime) insert A(start_time,end_time) select '2011-6-30 09:00:00','2011-6-30 10:00:00' union all select '2011-6-30 09:50:00','2011-6-30 12:00:00' union all select '2011-6-30 13:00:00','2011-6-30 18:00:00' select sum(case when b2.end_time>b1.start_time then datediff(ss,b2.end_time,b1.start_time)+ datediff(ss,b1.start_time,b1.end_time) else datediff(ss,b2.start_time,b2.start_time)+ datediff(ss,b1.start_time,b1.end_time) end ) from A b1,A b2 where b1.id=b2.id+1
------解决方案--------------------
Create table tb(bdate datetime,edate datetime) insert into tb select '2011-6-30 09:00:00','2011-6-30 10:00:00' insert into tb select '2011-6-30 09:50:00','2011-6-30 12:00:00' insert into tb select '2011-6-30 13:00:00','2011-6-30 18:00:00' select sum(datediff(minute,isnull(t2.edate,t1.bdate),t1.edate)) from tb t1 left join tb t2 on t1.bdate>t2.bdate and t1.bdate<t2.edate -- 480
------解决方案--------------------
create table #tb(startDate datetime,EndDate datetime) insert #tb select '2011-6-30 09:00:00','2011-6-30 10:00:00' union all select '2011-6-30 09:50:00','2011-6-30 12:00:00' union all select '2011-6-30 13:00:00','2011-6-30 18:00:00' union all select '2011-6-30 17:30:00','2011-6-30 19:00:00' union all select '2011-6-30 20:00:00','2011-6-30 21:00:00' ;with tempA as (select row_number()over(order by startdate) as num,* from #tb) , tempB as(select t1.startdate,case when t1.enddate>isnull(t2.startdate,'9999-12-31') then t2.startdate else t1.enddate end as enddate from tempA as t1 left join tempA as t2 on t1.num=t2.num-1) select sum(datediff(mi,startdate,enddate)) as sumTime from tempB