但SQL本身没有序运算,这个思路就要写成上面那种嵌套的方式,很费劲。如果是JAVA+SQL环境,还可以用润乾集算器简化:
A1 $select num1,num2 from tb order by num1
A2 =a=0
A3 =A1.groups@o(a+=if(num1>num2[-1],1,0):xh;min(num1):num1,max(num2):num2)
with tt as
( select 1 xh, 10015 num1, 30080 num2 from dual union all
select 2 xh, 20022 num1, 50090 num2 from dual union all
select 3 xh, 60250 num1, 90900 num2 from dual
)
select row_number()over(order by rn) xh,
min(num1) num1,
max(num2) num2
from
(
select num1,
num2,
last_value(case when rn is not null then rn end ignore nulls)over(order by rownum) rn
from
(
select num1,
num2,
lag(num2)over(order by xh),
(case when num1 - lag(num2)over(order by xh) < 0 then null else xh end) rn
from tt
) t
) t1
group by rn