67,513
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select 'a' id, 1 val, 1 ord from dual union all
select 'a' id, 1 val, 2 ord from dual union all
select 'b' id, 2 val, 3 ord from dual union all
select 'b' id, 3 val, 4 ord from dual union all
select 'a' id, 4 val, 5 ord from dual
)
, tab2 as (
select t1.*,
decode(lag(t1.id) over(order by t1.ord), t1.id, 0, 1) lag
from tab1 t1
)
, tab3 as (
select t1.*,
sum(t1.lag) over(order by t1.ord) sm
from tab2 t1
)
select t1.id,
sum(t1.val)
from tab3 t1
group by t1.id, t1.sm
order by t1.sm
;