17,377
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select 'p1a0' pn, 1 cnt from dual union all
select 'p1a1' pn, 1 cnt from dual union all
select 'p1b0' pn, 2 cnt from dual union all
select 'p2a0' pn, 1 cnt from dual union all
select 'p2a1' pn, 1 cnt from dual
)
, tab2 as (
select t1.*,
lag(t1.cnt) over(partition by substr(t1.pn, 1, length(t1.pn) - 2) order by substr(t1.pn, -2)) lg
from tab1 t1
)
select *
from tab2 t1
where t1.lg is not null
and t1.lg != cnt
;