接
http://blog.csdn.net/siegebaoniu/article/details/7752636
还有一种思路,比上个方法要简便。
在原表上对time排序,给一个flag,如果遇到start就+1,遇到end就-1.
这样的话,对于前N条,若flag>0,则表示服务器有人占用,若flag=0,则表示此时空余。
然后算出总时间和空余的时间,相减即可。
with cte as
(
select rn=ROW_NUMBER() over (order by recordtime),
statusvalue=case recordstatus when 'start' then 1 else -1 end,
recordtime
from TRecords
)
select t1.rn,t1.recordtime,SUM (t2.statusvalue) as flag from cte t1
inner join cte t2 on t1.rn>=t2.rn
group by t1.rn,t1.recordtime
其中,0表示断点。1-0,1-2-3-2-1-0,1-X 分成3段。严格意义上讲,最后一个数也应该是0,表示结束时间。
而从0开始,到下一个数,应该就是孤岛,表示这段时间服务器没人在使用。我们把这个时间算出。
with cte1 as
(
select rn=ROW_NUMBER() over (order by recordtime),
statusvalue=case recordstatus when 'start' then 1 else -1 end,
recordtime
from TRecords
)
,cte2 as
(
select t1.rn,t1.recordtime,SUM (t2.statusvalue) as flag from cte1 t1
inner join cte1 t2 on t1.rn>=t2.rn
group by t1.rn,t1.recordtime
)
select t1.recordtime as st
,(select MIN(recordtime) from cte2 t2 where t1.rn<t2.rn and t2.flag=1) as en
from cte2 t1 where t1.flag=0
计算两段孤岛,得出:10+10min=20min
总时间,取一头一尾,1:20:00 = 80min
相减,得60min。
是不是比第一个方法要简单好多。。。