求一句SQL

Question

         有一张表 
         mac 是机器编号
         status 是机器的状态 有几种种 down 和run，stops
         time_key 是机器切换状态时。
          2008-12-12 12：12：12  就是指机器1在2008-12-12 12：12：12 把状态切换成down
                 time_key                     status             mac
        2008-12-12 12：12：12                  down               1
        2008-12-12 12：16：12                  stops              1
        2008-12-12 12：20：12                  run                1
        2008-12-12 12：30：12                  run                1
         
        想得到离down最近的run的时间，也就是第三笔。

Answer 1

select time_key,status,mac from 
(select time_key,status,lag(status) over(partition by mac order by time_key) status2,mac from tb where status in ('down','run'))
where status = 'run' and status2 = 'down';

Answer 2

18:08:58 scott@TUNGKONG> select * from tb;TIME_KEY             STATU        MAC
-------------------- ----- ----------
2008-12-12 12:12:12  down           1
2008-12-12 12:16:12  stops          1
2008-12-12 12:20:12  run            1
2008-12-12 12:30:12  run            1已用时间:  00: 00: 00.00
18:09:05 scott@TUNGKONG> select time_key,status,mac from
18:09:13   2  (select time_key,status,lag(status) over(partition by mac order by time_key) status2,mac from tb where status in ('down','run'))
18:09:13   3  where status = 'run' and status2 = 'down';TIME_KEY             STATU        MAC
-------------------- ----- ----------
2008-12-12 12:20:12  run            1已用时间:  00: 00: 00.00

Answer 3

select max(time_key)
from  test
where mac =
(
select distinct mac
from test
where status='down'
)
and status='run'
group by mac,time_key

Answer 4

select max(time_key),status,mac
from  test
where mac =
(
select distinct mac
from test
where status='down'
)
and status='run'
group by mac,time_key,mac

Answer 5

该run时间要在down之后？而且down可能不唯一对吧with tt as(select to_date('2008-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss')time_key,'down' status,1 mac from dual
  union all select to_date('2008-12-12 12:16:12','yyyy-mm-dd hh24:mi:ss'),'stops',1 from dual
  union all select to_date('2008-12-12 12:20:12','yyyy-mm-dd hh24:mi:ss'),'run',1 from dual
  union all select to_date('2008-12-12 12:30:12','yyyy-mm-dd hh24:mi:ss'),'run',1 from dual)select * 
from tt t1 left join tt t2
on t1.status='down'
  and t1.mac=t2.mac
  and t1.time_key<t2.time_key
  and t2.status='run'
  and not exists(select 1 from tt
    where mac=t1.mac and time_key>t1.time_key
      and time_key<t2.time_key 
      and status='run')

Answer 6

加上down开始的时间
11:15:19 scott@TUNGKONG> select * from tb;TIME_KEY             STATU        MAC
-------------------- ----- ----------
2008-12-12 12:12:12  down           1
2008-12-12 12:16:12  stops          1
2008-12-12 12:20:12  run            1
2008-12-12 12:30:12  run            1
2008-12-12 12:12:12  down           1已用时间:  00: 00: 00.00
11:15:22 scott@TUNGKONG> select time_key2,time_key,status,mac from
11:15:28   2  (select time_key,status,lag(status) over(partition by mac order by time_key) status2,lag(time_key) over(partition by mac order by time_key) time_key2,mac from tb where status in ('down','run'))
11:15:28   3  where status = 'run' and status2 = 'down';TIME_KEY2            TIME_KEY             STATU        MAC
-------------------- -------------------- ----- ----------
2008-12-12 12:12:12  2008-12-12 12:20:12  run            1已用时间:  00: 00: 00.01

Answer 7

如果数据时这样
2008-12-12 12:12:12  down           1
2008-12-12 12:16:20  stops            1
2008-12-12 12:12:30  down           1
2008-12-12 12:16:12  stops            1
2008-12-12 12:20:12  run               1
2008-12-12 12:30:12  run               1
2008-12-12 12:12:12  down           1你只能得到2008-12-12 12:12:30       2008-12-12 12:20:12  run               1
其实我希望的是：只计算从down开始离它最近的run 
如上数据，应该得到 
2008-12-12 12:12:12    2008-12-12 12:20:12  run               1
2008-12-12 12:12:30    2008-12-12 12:20:12  run               1

Answer 8

select distinct time_key1,first_value(time_key2) over(partition by time_key1 order by time_key2) time_key2,status,mac from 
(select a.time_key time_key1,b.time_key time_key2,b.status,b.mac from
(select distinct time_key,status,mac from tb where status = 'down') a,
(select distinct time_key,status,mac from tb where status = 'run') b
where a.mac = b.mac);

Answer 9

14:05:25 scott@TUNGKONG> select * from tb;TIME_KEY             STATU        MAC
-------------------- ----- ----------
2008-12-12 12:12:12  down           1
2008-12-12 12:16:20  stops          1
2008-12-12 12:12:30  down           1
2008-12-12 12:20:12  run            1
2008-12-12 12:30:12  run            1
2008-12-12 12:12:12  down           1已选择6行。已用时间:  00: 00: 00.00
14:05:28 scott@TUNGKONG> select distinct time_key1,first_value(time_key2) over(partition by time_key1 order by time_key2) time_key2,status,mac from
14:05:36   2  (select a.time_key time_key1,b.time_key time_key2,b.status,b.mac from
14:05:36   3  (select distinct time_key,status,mac from tb where status = 'down') a,
14:05:36   4  (select distinct time_key,status,mac from tb where status = 'run') b
14:05:36   5  where a.mac = b.mac);TIME_KEY1            TIME_KEY2            STATU        MAC
-------------------- -------------------- ----- ----------
2008-12-12 12:12:30  2008-12-12 12:20:12  run            1
2008-12-12 12:12:12  2008-12-12 12:20:12  run            1已用时间:  00: 00: 00.00