查找SQL中连续递增数字的最长序列

取一杯天上的水果

对于这个例子说，我有两个字段的表，AREA varchar(30)和OrderNumber INT。
* M- k. p8 S& o1 _" h6 q该表具有以下数据
AREA      | OrderNumber
Fontana   |       32
Fontana   |       42
Fontana   |       76
5 r6 U- g* v+ h) n2 g* a" {% sFontana   |       12
+ p" a' g) b6 `4 r6 wFontana   |        3
Fontana   |       99
RC        |       32
RC        |        1
2 b& R0 M. m0 {. X& v  S! K$ hRC        |        8
& A7 N6 p$ u- t- T; s, DRC        |        9
RC        |        4
3 b( Q+ J9 ]2 z) c# w) \我想回来
: l8 J$ v& i. F5 F$ B我想返回的结果是每个区域递增连续值的最长长度。对于Fontana it is 3 (32, 42, 76)。For RC it is 2 (8,9)
( q# ?" V0 u) D7 @' aAREA    | LongestLength
. T& V5 Q" p3 AFontana |          3
RC      |          2
, F, e- p! L, X' ]/ ^我将如何在MS Sql 2005上执行此操作？
( O& k7 b2 ~2 D& y- W; y& U               
解决方案:
               

7 f" \5 m& d: h, S# \! u
3 s% T# C1 `: }6 b5 [. ~- e3 _5 r+ {                一种方法是使用遍历每一行的递归CTE。如果该行符合条件（增加同一区域的订单号），则将链长增加一。如果没有，则启动一个新链：
; with  numbered as
        (
6 q) h. w$ E3 v1 l        select  row_number() over (order by area, eventtime) rn
        ,       *
2 n5 k- E- M4 w/ r5 p' p6 v: B        from    Table1
        )
( L: h2 r7 P# O1 c" Y4 z,       recurse as
        (
" N/ O& h& g$ M5 T        select  rn
; ^5 T, R6 ~' Z1 U1 `% ^& K( p        ,       area
        ,       OrderNumber
" z& Y& ?0 |# U& n+ l        ,       1 as ChainLength
$ V2 e& x4 ^, [9 j        from    numbered
        where   rn = 1
        union all
        select  cur.rn
        ,       cur.area
' p9 Q9 w  ~, n4 |8 d) _        ,       cur.OrderNumber
        ,       case
/ g* T  Q( s0 Z+ z7 U5 o                when cur.area = prev.area
                     and cur.OrderNumber > prev.OrderNumber
                     then prev.ChainLength + 1
                else 1
                end
        from    recurse prev
        join    numbered cur
+ {& A; F$ @8 V# M2 J  G        on      prev.rn + 1 = cur.rn
) g9 r' W* g$ T4 k6 R( A9 H) a        )
- S- M& r! X3 s$ g1 }5 ]select  area
* Z) f% x' }7 M" g3 G" \, c0 h,       max(ChainLength)
from    recurse
1 h0 C3 G* E& J2 F) u  pgroup by
$ k1 c$ i/ \" p! ?% `* ]5 C$ J        area
SQL Fiddle的实时示例。
. F6 p* [, V& t另一种方法是使用查询查找“中断”，即以相同区域的顺序编号递增的序列结束的行。中断之间的行数是长度。
4 k/ l1 n. [7 f# Q1 P7 Q; with  numbered as
        (
        select  row_number() over (order by area, eventtime) rn
        ,       *
. C/ D7 e9 u: b6 d3 h4 U+ e' q2 X        from    Table1 t1
        )
        -- Select rows that break an increasing chain
8 B9 F9 ?4 E2 h6 B# D$ Q3 f,       breaks as
        (
8 h* \! O9 o$ f: Z        select  row_number() over (order by cur.rn) rn2
9 ?- A  f3 q$ b        ,       cur.rn
        ,       cur.Area
        from    numbered cur
5 y% `& j3 p- D6 S3 w% s/ g7 z        left join
  o  h2 N  h! M& M: ]" B# y                numbered prev
        on      cur.rn = prev.rn + 1
) l5 T$ u1 l7 X7 _! Z1 H5 Z0 O$ T        where   cur.OrderNumber  prev.Area
7 d. }5 o4 g5 Q# ?4 d" x! c                or prev.Area is null
2 k& d! X) y1 c7 O: z4 B6 C1 a        )
        -- Add a final break after the last row
5 r4 a: K. {2 D% b7 T2 y9 x,       breaks2 as
        (
        select  *
        from    breaks
        union all
        select  count(*) + 1
! l" Z: @0 ~7 H9 f        ,       max(rn) + 1
        ,       null
        from    breaks
        )
# U% B/ Z6 q% qselect  series_start.area
,       max(series_end.rn - series_start.rn)
from    breaks2 series_start
join    breaks2 series_end
on      series_end.rn2 = series_start.rn2 + 1
group by
        series_start.area
$ K5 F( k( R  c+ Z, R# q" j  [SQL Fiddle的实时示例。