select t3.UnitName,t3.DeviceName, (ltrim(t3.seconds/3600)+'小时'+ltrim(t3.seconds%3600/60)+'分钟'+ltrim(t3.seconds%60)+'秒') as Interrupt
from
(
select
t1.CurDT as StartDT,t2.CurDT as EndDT,t1.NetState as StartState,t2.NetState as EndState, DATEDIFF (ss,t1.CurDT,t2.CurDT) as seconds,t1.UnitName,t1.DeviceName
from
(
select ROW_NUMBER() OVER(ORDER BY StateHisID ASC) AS 'RowIndex',a.CurDT,b.UnitName,c.DeviceName,a.NetState
from S_StateHis a,S_Unit b,S_Device c where a.DeviceID=500 and (a.CurDT between '2013-12-01' and '2013-12-15') and a.UnitID=b.UnitID and a.DeviceID=c.DeviceID
) t1,
(
select ROW_NUMBER() OVER(ORDER BY StateHisID ASC) AS 'RowIndex',CurDT,NetState
from S_StateHis where DeviceID=500 and (CurDT between '2013-12-01' and '2013-12-15')
) t2
where t1.RowIndex+1=t2.RowIndex
) t3 where t3.StartState='断开' and t3.EndState='正常'
from
(
select
t1.CurDT as StartDT,t2.CurDT as EndDT,t1.NetState as StartState,t2.NetState as EndState, DATEDIFF (ss,t1.CurDT,t2.CurDT) as seconds,t1.UnitName,t1.DeviceName
from
(
select ROW_NUMBER() OVER(ORDER BY StateHisID ASC) AS 'RowIndex',a.CurDT,b.UnitName,c.DeviceName,a.NetState
from S_StateHis a,S_Unit b,S_Device c where a.DeviceID=500 and (a.CurDT between '2013-12-01' and '2013-12-15') and a.UnitID=b.UnitID and a.DeviceID=c.DeviceID
) t1,
(
select ROW_NUMBER() OVER(ORDER BY StateHisID ASC) AS 'RowIndex',CurDT,NetState
from S_StateHis where DeviceID=500 and (CurDT between '2013-12-01' and '2013-12-15')
) t2
where t1.RowIndex+1=t2.RowIndex
) t3 where t3.StartState='断开' and t3.EndState='正常'
select o.sta_time,min(o.end_time) as end_time from
(select a.time as sta_time ,b.time as end_time from a a left join a b
on a.time <b.time) o
group by o.sta_time
--sum(end_time -sta_time )
(select *,row_number() over(order by CurDT) 'rn'
from [表名] where NetState='断开'
union all
select *,row_number() over(order by CurDT)+1 'rn'
from [表名] where NetState='正常') t
order by t.rn
(select *,row_number() over(order by CurDT) as rn
from [表名]) a
inner join
(
select *,row_number() over(order by CurDT) as rn
from [表名]
) b
on a.DeviceID = b.DeviceID and a.CurDT = b.CurDT and a.rn=b.rn+1
and a.NetStat='断开' and b..NetStat='正常'