How can I get the followling results?

根據標誌位獲得裝置發生故障時間和恢復故障時間的列表
http://www.itpub.net/464005.html

另外此文也可參考http://www.itpub.net/167645.html

[@more@]

I have a table, utc_devicestates, which includes three columns,

deviceid, errorcode, currenttime

this is a table containing data of device status. When errorcode
is 1, it indicates that device is in error status. When errorcode
is 0, it incidates the device is in good status.

Now, I need to write a procedure to get the device status, When
I input deviceid, the procedure can return me the status history.

Such as the data in table utc_devicestates is:
deviceid errorcode currenttime
988904 1 2005-7-25 10:33:36
988904 0 2005-7-25 10:34:36
988904 1 2005-7-25 10:35:36
988904 1 2005-7-25 10:36:36
988904 0 2005-7-25 10:37:36
988904 1 2005-7-25 10:38:36
988904 1 2005-7-25 10:39:36
988904 0 2005-7-25 10:40:36
988904 0 2005-7-25 10:41:36

Then I want to get the result:
deviceid, errortime, recoverytime
988904 2005-7-25 10:33:36 2005-7-25 10:34:36
988904 2005-7-25 10:35:36 2005-7-25 10:37:36
988904 2005-7-25 10:38:36 2005-7-25 10:40:36

How can I got the result through sql statement?
Any help is thankful!

【以下是在hmxxyy解答的基礎上進行的修改】

SQL> create table tdev (id number, ec number , n number);

表已建立。

SQL> insert into tdev select 1, floor(dbms_random.value(0,2)),rownum from dual connect by rownum<20;

已建立19行。

SQL> select * from tdev;

ID EC N
---------- ---------- ----------
1 0 1
1 1 2
1 1 3
1 0 4
1 1 5
1 1 6
1 0 7
1 1 8
1 1 9
1 0 10
1 1 11
1 0 12
1 0 13
1 0 14
1 0 15
1 0 16
1 0 17
1 1 18
1 1 19

已選擇19行。

select id, min(n) errortime, recoverytime from(
select id, n , (select min(n)
from tdev x where x.n > y.n
and ec = 0 and id=y.id) recoverytime
from tdev y
where ec = 1
) group by id, recoverytime
order by id, recoverytime nulls last

SQL> select id, min(n) errortime, recoverytime from(
2 select id, n , (select min(n)
3 from tdev x where x.n > y.n
4 and ec = 0 and id=y.id) recoverytime
5 from tdev y
6 where ec = 1
7 ) group by id, recoverytime
8 order by id, recoverytime nulls last
9 /

ID ERRORTIME RECOVERYTIME
---------- ---------- ------------
1 2 4
1 5 7
1 8 10
1 11 12
1 18