关于SQL求时间差总和的有关问题

关于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

如何计算他们之间所包括的总时间？

谢谢各位了先。

------解决方案--------------------

SQL code


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

------解决方案--------------------

SQL code


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

------解决方案--------------------

SQL code


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

关于SQL求时间差总和的有关问题

相关推荐