17,082
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select 1 well_id, 1 type, 11 time from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 2 type, 8 ord from dual
)
, tab2 as (
select t1.*,
sum(decode(t1.type, 1, 0, 1)) over(partition by t1.well_id order by t1.time desc) sm,
row_number() over(partition by t1.well_id order by t1.time desc) rn
from tab1 t1
)
, tab3 as (
select t1.*,
decode(1, first_value(t1.type) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn)
,first_value(t1.rn) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn) - 1
,max(t1.rn) over(partition by t1.well_id )) fv
from tab2 t1
where t1.sm != 0
order by t1.well_id, t1.time desc
)
select * from tab3 t1 where t1.rn = fv
;
;
with tab1 as (
select 1 well_id, 1 type, 11 time from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 2 type, 8 ord from dual
)
, tab2 as (
select t1.*,
sum(decode(t1.type, 1, 0, 1)) over(partition by t1.well_id order by t1.time desc) sm,
row_number() over(partition by t1.well_id order by t1.time desc) rn
from tab1 t1
)
, tab3 as (
select t1.*,
decode(1, first_value(t1.type) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn)
,first_value(t1.rn) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn) - 1
,max(t1.rn) over(partition by t1.well_id )) fv
from tab2 t1
where t1.sm != 0
order by t1.well_id, t1.time desc
)
select * from tab3 t1 where t1.rn = fv
;
;
with tab1 as (
select 1 id, 1 type, 11 ord from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 1 type, 8 ord from dual
)
, tab2 as (
select t1.id,
t1.type,
t1.ord,
row_number() over(partition by t1.id order by t1.ord desc) rn
from tab1 t1
order by t1.id, ord desc
)
select distinct
t1.id,
first_value(t1.ord) over(partition by t1.id order by level desc) time
from tab2 t1
start with t1.rn = 1
connect by prior t1.id = t1.id
and prior t1.rn + 1 = t1.rn
and not (t1.type != 2 and prior t1.type = 2)
;