请教大家
with a as
(select 111 id, 0 value from dual
union all
select 1111 id, 1 value from dual
union all
select 222 id, 3 value from dual
union all
select 333 id, 5 value from dual
union all
select 444 id, 1 value from dual
union all
select 555 id, 0 value from dual
union all
select 666 id, 2 value from dual
union all
select 777 id, 0 value from dual
union all
select 888 id, 1 value from dual
union all
select 999 id, 0 value from dual)
select * from a
大家可以看到 1111的值 1 和444的值1差两条2记录
444的值1 和888的值 1 差3条记录
请教大家如何查出 相邻2条1之间的最大值
例如上面的结果 就是查出 两条相邻1之间的 最大记录数为3
------解决方案--------------------
select max(rn)
from (select rn - lag(rn) over(order by rownum) - 1 rn
from (select a.*, rownum rn from a)
where value = 1)