3,490
社区成员
发帖
与我相关
我的任务
分享
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 rn
MAC_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分钟