一个表记录如下:state为1表示设备出现故障,为0表示设备恢复正常.changeTime表示改变的时间.现在想查询某段时间内所有设备出现故障的时间以及恢复时间.用sql语句怎么写?
devId state changeTime
15 1 2007-04-19 13:00:00
15 0 2007-04-19 15:00:00
16 1 2007-04-19 13:00:00
16 0 2007-04-19 14:00:00
15 1 2007-04-19 15:50:00
15 0 2007-04-19 18:00:00
devId state changeTime
15 1 2007-04-19 13:00:00
15 0 2007-04-19 15:00:00
16 1 2007-04-19 13:00:00
16 0 2007-04-19 14:00:00
15 1 2007-04-19 15:50:00
15 0 2007-04-19 18:00:00
, (select top 1 changeTime from tbl where a.devId =devId and a.changeTime >changeTime and state =0 order by changeTime ) as 恢复时间
from tbl a
where state =1
and changetime between '' and ''
insert T select 15, 1, '2007-04-19 13:00:00'
union all select 15, 0, '2007-04-19 15:00:00'
union all select 16, 1, '2007-04-19 13:00:00'
union all select 16, 0, '2007-04-19 14:00:00'
union all select 15, 1, '2007-04-19 15:50:00'
union all select 15, 0, '2007-04-19 18:00:00'select devId, changeTime as 故障时间,
恢复时间=(select top 1 changeTime from T where state=0 and devId=tmp.devId and changeTime>=tmp.changeTime order by changeTime)
from T as tmp
where state=1--result
devId 故障时间 恢复时间
----------- ------------------------------------------------------ ------------------------------------------------------
15 2007-04-19 13:00:00.000 2007-04-19 15:00:00.000
16 2007-04-19 13:00:00.000 2007-04-19 14:00:00.000
15 2007-04-19 15:50:00.000 2007-04-19 18:00:00.000(3 row(s) affected)