How can I get the followling results? - Oracle开发 - ITPUB论坛

www.wwf.co
资深会员

精华贴数 2
个人空间 0
技术积分 2200 (759)
社区积分 2306 (587)
注册日期 2003-11-21
论坛徽章:5

#1

使用道具

发表于 2005-12-9 16:37

How can I get the followling results?

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!

__________________

http://blog.itpub.net/www.wwf.cooracle_wwf@hotmail.com这是最好的时代，也是最坏的时代

只看该作者

hmxxyy
高级会员

精华贴数 2
个人空间 0
技术积分 2381 (687)
社区积分 19 (8143)
注册日期 2004-9-17
论坛徽章:5

#2

使用道具

发表于 2005-12-9 17:15

select deviceid, currenttime errrotime, (select min(currenttime) from utc_device x where errorcode = 0 and
x.currenttime > y.current_time) recoverytime
from utc_device y
where error_code = 1

__________________

http://hmxxyy.itpub.net只听胡琴声缠绵宛转，却是一曲《凤求凰》...

只看该作者

jackywood
高级会员

精华贴数 2
个人空间 0
技术积分 4837 (300)
社区积分 13157 (125)
注册日期 2004-10-18
论坛徽章:31

#3

使用道具

发表于 2005-12-9 17:38

QUOTE:

最初由 hmxxyy 发布
select deviceid, currenttime errrotime, (select min(currenttime) from utc_device x where errorcode = 0 and
x.currenttime > y.current_time) recoverytime
from utc_device y
where error_code = 1

稍微修改一下, 因为表中的deviceid的值应该不是单一的
select deviceid, currenttime errrotime,
      (select min(currenttime)
            from utc_device x
         where x.currenttime > y.current_time
            and errorcode = 0
            and deviceid=y.deviceid
      ) recoverytime
from utc_device y
where error_code = 1

__________________

不恋雁塔荣名解守鱼渊寒素msn : jackywood_24@hotmail.comblog: http://jackywood.itpub.net

只看该作者

hmxxyy
高级会员

精华贴数 2
个人空间 0
技术积分 2381 (687)
社区积分 19 (8143)
注册日期 2004-9-17
论坛徽章:5

#4

使用道具

发表于 2005-12-9 17:49

改得好

__________________

http://hmxxyy.itpub.net只听胡琴声缠绵宛转，却是一曲《凤求凰》...

只看该作者

lastwinner
路边野花不要，踩！

世本冇谱，靠者多便有

精华贴数 18
个人空间 832
技术积分 42266 (20)
社区积分 28888 (36)
注册日期 2002-11-27
论坛徽章:195

#5

使用道具

发表于 2005-12-9 18:50

创建一个类似的表来做试验

PHP code:



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, n errortime, 


(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





SQL> select id, n errortime,


  2  (select min(n)


  3  from tdev x


  4  where x.n > y.n


  5  and ec = 0


  6  and id=y.id


  7  ) recoverytime


  8  from tdev y


  9  where ec = 1


 10  /





        ID  ERRORTIME RECOVERYTIME


---------- ---------- ------------


         1          2            4


         1          3            4


         1          5            7


         1          6            7


         1          8           10


         1          9           10


         1         11           12


         1         18


         1         19





已选择9行。





..............................

以上结果并不满足楼主的要求

__________________

javascript:var c=document.body;c.innerHTML=c.innerHTML.replace(/(\d{2})\d{3} ($\d{2}$)/g,"$1888 ($2)");alert(c);

只看该作者

lastwinner
路边野花不要，踩！

世本冇谱，靠者多便有

精华贴数 18
个人空间 832
技术积分 42266 (20)
社区积分 28888 (36)
注册日期 2002-11-27
论坛徽章:195

#6

使用道具

发表于 2005-12-9 19:08

PHP code:



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


.........................................................

__________________

javascript:var c=document.body;c.innerHTML=c.innerHTML.replace(/(\d{2})\d{3} ($\d{2}$)/g,"$1888 ($2)");alert(c);

只看该作者

hmxxyy
高级会员

精华贴数 2
个人空间 0
技术积分 2381 (687)
社区积分 19 (8143)
注册日期 2004-9-17
论坛徽章:5

#7

使用道具

发表于 2005-12-9 20:01

不好意思，没仔细看它有连续的0和1

__________________

http://hmxxyy.itpub.net只听胡琴声缠绵宛转，却是一曲《凤求凰》...

只看该作者

lastwinner
路边野花不要，踩！

世本冇谱，靠者多便有

精华贴数 18
个人空间 832
技术积分 42266 (20)
社区积分 28888 (36)
注册日期 2002-11-27
论坛徽章:195

#8

使用道具

发表于 2005-12-9 20:21

本想用分析函数试解（sum、lead、rownum）
因为这个题非常类似于
http://blog.itpub.net/post/7102/44843 (http://www.itpub.net/435578.html)
http://blog.itpub.net/post/4791/27805 (http://www.itpub.net/showthread.php?s=&threadid=354052)

可折腾半天没弄出来，只好作罢

还是在大牛的基础上改比较省事

__________________

javascript:var c=document.body;c.innerHTML=c.innerHTML.replace(/(\d{2})\d{3} ($\d{2}$)/g,"$1888 ($2)");alert(c);

只看该作者

jackywood
高级会员

精华贴数 2
个人空间 0
技术积分 4837 (300)
社区积分 13157 (125)
注册日期 2004-10-18
论坛徽章:31

#9

使用道具

发表于 2005-12-10 01:01

QUOTE:

最初由 lastwinner 发布
创建一个类似的表来做试验
PHP code:
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, n errortime, (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 SQL> select id, n errortime, 2 (select min(n) 3 from tdev x 4 where x.n > y.n 5 and ec = 0 6 and id=y.id 7 ) recoverytime 8 from tdev y 9 where ec = 1 10 / ID ERRORTIME RECOVERYTIME ---------- ---------- ------------ 1 2 4 1 3 4 1 5 7 1 6 7 1 8 10 1 9 10 1 11 12 1 18 1 19 已选择9行。 ..............................
以上结果并不满足楼主的要求

没仔细看楼主的要求, 只看了二楼的
用一下你的例子, 这样的sql应该可以了吧
select id, min(errortime) as errortime, recoverytime
from (
select id,
   n errortime,
   (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

__________________

不恋雁塔荣名解守鱼渊寒素msn : jackywood_24@hotmail.comblog: http://jackywood.itpub.net

只看该作者

hmxxyy
高级会员

精华贴数 2
个人空间 0
技术积分 2381 (687)
社区积分 19 (8143)
注册日期 2004-9-17
论坛徽章:5

#10

使用道具

发表于 2005-12-10 23:09

with x as
(select id, ec, n, prv, rownum rn from (
select tdev.*,
lag(ec) over (partition by id order by n) prv
from tdev
order by id, n
) where ec <> prv and not (ec=0 and rownum = 1)
) select id, max(decode(ec, 1, n)), max(decode(ec, 0, n))
from x
group by id, ec+rn
/

这个用分析函数

__________________

http://hmxxyy.itpub.net只听胡琴声缠绵宛转，却是一曲《凤求凰》...

只看该作者