关于SQL求时间差总和的问题
现在有一下三个时间段：
开始 结束
2011-6-30 09:00:00 2011-6-30 10:00:00
2011-6-30 09:50:00 2011-6-30 12:00:00
2011-6-30 13:00:00 2011-6-30 18:00:00
如何计算他们之间所包括的总时间？
谢谢各位了先。
[解决办法]
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 allselect '2011-6-30 09:50:00','2011-6-30 12:00:00'union allselect '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 #tbselect '2011-6-30 09:00:00','2011-6-30 10:00:00' union allselect '2011-6-30 09:50:00','2011-6-30 12:00:00' union allselect '2011-6-30 13:00:00','2011-6-30 18:00:00' union allselect '2011-6-30 17:30:00','2011-6-30 19:00:00' union allselect '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
