我有一个表:时间 运行状态
2012-7-26 9:58 Run
2012-7-26 9:57 Stop
2012-7-26 9:56 Run
2012-7-26 9:55 Run
2012-7-26 9:53 Run怎么用SQL语句统计开机多长时间和停机多长时间???
2012-7-26 9:58 Run
2012-7-26 9:57 Stop
2012-7-26 9:56 Run
2012-7-26 9:55 Run
2012-7-26 9:53 Run怎么用SQL语句统计开机多长时间和停机多长时间???
from tb a
left join b on a.fid=b.fid+1
where a.fstat='run' and b.fstat='stop'其他数据就好办了
俺是这样做的。 select sum(DateDiff('s',t3.sdt,t3.edt)) as seconds
from(
select min(t2.CurDT) as sdt, max(t2.CurDT) as edt
from (
select t1.PDCHistoryID, t1.CurDT,(t1.PDCHistoryID - t1.RowIndex ) as delta
from
(
select PDCHistoryID,RowIndex,CurDT from temp order by PDCHistoryID ASC
) AS t1
)
AS t2
group by t2.delta
) as t3
改成select a.CurDT-b.CurDT
from History as a
left join History b on a.PDCHistoryID=b.PDCHistoryID+1
where a.RunState='Run' and b.RunState='Stop'计算不准确!!!!!PDCHistoryID 自动增加ID。
CurDT 时间
RunState 运行状态。