求ID连续的最大个数

问题描述：表machine

id     other......

1       x

2       x

4       x

5       x

6       x

求ID连续的最大个数，对上例来说，4/5/6 是三个连续，最后应得值3

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 

rownum函数在SQL2000应该是没有的

转载于:https://www.cnblogs.com/iupi0203/archive/2008/09/10/1288345.html