我有一些时间段,怎么能计算出去重复的总时间,比如
a b
9:00:00 10:00:00
8:00:00 9:30:00
11:00:00 12:00:00
结果应该是3小时,如果把时间段查出来在外面计算太慢了,能不能直接在SQL里面算好
--代码如下
with tb(a,b)as(
select '2001-01-01 6:00:00','2001-01-01 7:00:00' union
select '2001-01-01 9:00:00','2001-01-01 10:00:00' union
select '2001-01-01 8:00:00','2001-01-01 9:30:00' union
select '2001-01-01 8:00:00','2001-01-01 10:30:00' union
select '2001-01-01 11:00:00','2001-01-01 12:00:00'),tc as(
select CONVERT(datetime,a)a,CONVERT(datetime,b)b,ROW_NUMBER() over(order by convert(datetime,a),convert(datetime,b))num from tb
),td as(
select * from tc where num=1
union all
select case when tc.a between td.a and td.b then td.a else tc.a end
,case when tc.a between td.a and td.b then case when tc.b>td.b then tc.b else td.b end else tc.b end,tc.num
from td,tc where td.num=tc.num-1)
select a,max(b)b from td
group by a