求一句SQL

Question

E_status  状态  STOPS, RUN,DOWN,SETUP,DEA  五种状态
e_id   机台编号 A1,A2,A3,B1,B2,B3
l_id    线别 A ,B ，一条线可以有多个机台
start_time    开始时间,每次切换状态的时间要得到每次距离DOWN最近的RUN的时间，如果没有距离DOWN最近的时间，则默认今天的最大时间
l_id       e_id      E_status           start_time                
A            A1        DOWN        2000-11-11   10:00:00      
A            A1         RUN        2000-11-11   11:00:00     
A            A1        DOWN        2000-11-11   12:00:00
A            A1       SETUP         2000-11-11   12:30:00        
A            A1       DOWN         2000-11-11   13:00:00    
A            A1        RUN         2000-11-11   15:00:00       
A            A1       DOWN         2000-11-11   16:00:00      
A            A2       DOWN         2000-11-11   10:00:00    
A            A2         RUN        2000-11-11   11:00:00    
A            A2        DOWN        2000-11-11   12:00:00      
A            A2         RUN        2000-11-11   13:00:00    
A            A2        DOWN        2000-11-11   14:00:00     以A为例 需要输出如下结果。
l_id       e_id      E_status           start_time               run_time
A            A1        DOWN    2000-11-11   10:00:00       2000-11-11   11:00:00     
A            A1        DOWN    2000-11-11   12:00:00       2000-11-11   15:00:00       
A            A1       DOWN     2000-11-11   13:00:00       2000-11-11   15:00:00  
A            A1       DOWN     2000-11-11   16:00:00       2000-11-11   23:59:59 
A            A2       DOWN     2000-11-11   10:00:00       2000-11-11   11:00:00
A            A2        DOWN    2000-11-11   12:00:00       2000-11-11   13:00:00  
A            A2        DOWN    2000-11-11   14:00:00       2000-11-11   23:59:59  

Answer 1

select a.*,
  nvl(b.start_time,trunc(a.start_time+1)-1/3600/24)run_time from tt a,tt b
where a.l_id=b.l_id(+)
  and a.e_id=b.e_id(+)
  and a.e_status='DOWN'
  and b.e_status(+)='RUN'
  and b.start_time(+)>a.start_time
  and not exists(
    select 1 from tt
      where e_status='RUN'
        and l_id=a.l_id
        and e_id=a.e_id
        and start_time>a.start_time
        and start_time<b.start_time)
order by 1,2,4

Answer 2

select distinct l_id,e_id,e_status,start_time,first_value(run_time) over(partition by l_id,e_id,start_time order by run_time) run_time from 
(select a.l_id,a.e_id,a.e_status,a.start_time,
case when b.start_time < a.start_time then substr(b.start_time,1,10)||' 23:59:59' else b.start_time end run_time from
(select distinct l_id,e_id,e_status,start_time from tb where e_status = 'DOWN') a,
(select distinct l_id,e_id,e_status,start_time from tb where e_status = 'RUN') b
where a.l_id = b.l_id and a.e_id = b.e_id
) order by 1,2,3,4,5;

Answer 3

15:30:57 scott@TUNGKONG> select * from tb;L_ID   E_ID   E_STATUS   START_TIME
------ ------ ---------- --------------------
A      A1     DOWN       2000-11-11 10:00:00
A      A1     RUN        2000-11-11 11:00:00
A      A1     DOWN       2000-11-11 12:00:00
A      A1     SETUP      2000-11-11 12:30:00
A      A1     DOWN       2000-11-11 13:00:00
A      A1     RUN        2000-11-11 15:00:00
A      A1     DOWN       2000-11-11 16:00:00
A      A2     DOWN       2000-11-11 10:00:00
A      A2     RUN        2000-11-11 11:00:00
A      A2     DOWN       2000-11-11 12:00:00
A      A2     RUN        2000-11-11 13:00:00
A      A2     DOWN       2000-11-11 14:00:00已选择12行。已用时间:  00: 00: 00.01
15:31:01 scott@TUNGKONG> select distinct l_id,e_id,e_status,start_time,first_value(run_time) over(partition by l_id,e_id,start_time order by run_time) run_time from
15:31:06   2  (select a.l_id,a.e_id,a.e_status,a.start_time,
15:31:06   3  case when b.start_time < a.start_time then substr(b.start_time,1,10)||' 23:59:59' else b.start_time end run_time from
15:31:06   4  (select distinct l_id,e_id,e_status,start_time from tb where e_status = 'DOWN') a,
15:31:06   5  (select distinct l_id,e_id,e_status,start_time from tb where e_status = 'RUN') b
15:31:06   6  where a.l_id = b.l_id and a.e_id = b.e_id
15:31:06   7  ) order by 1,2,3,4,5;L_ID   E_ID   E_STATUS   START_TIME           RUN_TIME
------ ------ ---------- -------------------- -----------------------------
A      A1     DOWN       2000-11-11 10:00:00  2000-11-11 11:00:00
A      A1     DOWN       2000-11-11 12:00:00  2000-11-11 15:00:00
A      A1     DOWN       2000-11-11 13:00:00  2000-11-11 15:00:00
A      A1     DOWN       2000-11-11 16:00:00  2000-11-11 23:59:59
A      A2     DOWN       2000-11-11 10:00:00  2000-11-11 11:00:00
A      A2     DOWN       2000-11-11 12:00:00  2000-11-11 13:00:00
A      A2     DOWN       2000-11-11 14:00:00  2000-11-11 23:59:59已选择7行。已用时间:  00: 00: 00.01

Answer 4

select distinct l_id,e_id,e_status,start_time,first_value(run_time) over(partition by l_id,e_id,start_time order by run_time) run_time from 
(select a.l_id,a.e_id,a.e_status,a.start_time,
case when b.start_time < a.start_time then substr(b.start_time,1,10)||' 23:59:59' else b.start_time end run_time from
(select distinct l_id,e_id,e_status,start_time from tb where e_status = 'DOWN') a,
(select distinct l_id,e_id,e_status,start_time from tb where e_status = 'RUN') b
where a.l_id = b.l_id and a.e_id = b.e_id
) order by 1,2,3,4,5;