问题描述如下:有表machine 有一个字段ID,数据如下:
ID
1
2
5
6
7
求这个表中最大的连续数。在machine 种1、2连续,5、6、7连续 ,所以该结果为:3。
请教高手后,Sql如下:
select
b.id,
count
(
*
)
count
from
( select id,rownum p from machine t order by id) a ,
( select id,rownum p from machine t order by id) b
where b.id - a.id = b.p - a.p and b.id - a.id > 0
group by b.id
( select id,rownum p from machine t order by id) a ,
( select id,rownum p from machine t order by id) b
where b.id - a.id = b.p - a.p and b.id - a.id > 0
group by b.id