ITPub博客

首页 > 应用开发 > IT综合 > How can I get the followling results?

How can I get the followling results?

原创 IT综合 作者:lastwinner 时间:2005-12-09 22:14:53 0 删除 编辑

根据标志位获得设备发生故障时间和恢复故障时间的列表
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-811529/,如需转载,请注明出处,否则将追究法律责任。

下一篇: HTMLEditor
请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    223
  • 访问量
    2813010