求距离最近的时间SQL

Question

表有4个栏位，以一条线为例
mac_id  varchar2(20)  --机器编号
d_date   date         --机器状态开始的时间
line_id varchar2(20)  --线别  一条线可以有多台机器
mac_status varchar2(20)    --机器的状态mac_id                          d_date                   line_id              mac_status 
a1                        2009-01-01 12:10:00                a                    colse
a1                        2009-01-01 12:20:00                a                    stops
a1                        2009-01-01 12:30:00                a                    opens
a2                        2009-01-01 12:30:00                a                    opens
a2                        2009-01-01 12:40:00                a                    colse
a2                        2009-01-01 12:50:00                a                    colse
a2                        2009-01-01 12:55:00                a                    opens以a2为例
    求a2 从colse  ---》opens的时间
     结果是
mac_id          d_date          line_id             o_time                cha_time    
a2         2009-01-01 12:40:00    a         2009-01-01 12:55:00         o_time -d_date
a2         2009-01-01 12:50:00    a         2009-01-01 12:55:00         o_time -d_date
   

Answer 1

select mac_id,min(d_date)d_date,line_id,
  max(d_date)o_time,round((max(d_date)-min(d_date))*24*60,2)||'分钟' cha_time
   from(
   
    select tt.*,
      row_number()over(partition by mac_id,line_id order by d_date)rn
    from tt)t
    
start with mac_status='close'
connect by prior rn=rn-1
  and prior mac_id=mac_id
  and prior mac_status<>'opens'
  
group by mac_id,line_id,connect_by_root rnMAC_ID D_DATE LINE_ID O_TIME CHA_TIME
a1 2009-1-1 12:10:00 a 2009-1-1 12:30:00 20分钟
a2 2009-1-1 12:50:00 a 2009-1-1 12:55:00 5分钟
a2 2009-1-1 12:40:00 a 2009-1-1 12:55:00 15分钟

Answer 2

wildwave，你是我的偶像！