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

Question

对于这个例子说，我有两个字段的表，AREA varchar(30)和OrderNumber INT。

该表具有以下数据

AREA      | OrderNumber
Fontana   |       32
Fontana   |       42
Fontana   |       76
Fontana   |       12
Fontana   |        3
Fontana   |       99
RC        |       32
RC        |        1
RC        |        8
RC        |        9
RC        |        4

我想回来

我想返回的结果是每个区域递增连续值的最长长度。对于Fontana it is 3 (32, 42, 76)。For RC it is 2 (8,9)

AREA    | LongestLength
Fontana |          3
RC      |          2

我将如何在MS Sql 2005上执行此操作？

Answer 1

一种方法是使用遍历每一行的递归CTE。如果该行符合条件（增加同一区域的订单号），则将链长增加一。如果没有，则启动一个新链：

; with  numbered as
        (
        select  row_number() over (order by area, eventtime) rn
        ,       *
        from    Table1
        )
,       recurse as
        (
        select  rn
        ,       area
        ,       OrderNumber
        ,       1 as ChainLength
        from    numbered
        where   rn = 1
        union all
        select  cur.rn
        ,       cur.area
        ,       cur.OrderNumber
        ,       case
                when cur.area = prev.area 
                     and cur.OrderNumber > prev.OrderNumber 
                     then prev.ChainLength + 1
                else 1
                end
        from    recurse prev
        join    numbered cur
        on      prev.rn + 1 = cur.rn
        )
select  area
,       max(ChainLength)
from    recurse
group by
        area

SQL Fiddle的实时示例。

另一种方法是使用查询查找“中断”，即以相同区域的顺序编号递增的序列结束的行。中断之间的行数是长度。

; with  numbered as
        (
        select  row_number() over (order by area, eventtime) rn
        ,       *
        from    Table1 t1
        )
        -- Select rows that break an increasing chain
,       breaks as
        (
        select  row_number() over (order by cur.rn) rn2
        ,       cur.rn
        ,       cur.Area
        from    numbered cur
        left join
                numbered prev
        on      cur.rn = prev.rn + 1
        where   cur.OrderNumber <= prev.OrderNumber
                or cur.Area <> prev.Area
                or prev.Area is null
        )
        -- Add a final break after the last row
,       breaks2 as
        (
        select  *
        from    breaks
        union all
        select  count(*) + 1
        ,       max(rn) + 1
        ,       null
        from    breaks
        )
select  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

SQL Fiddle的实时示例。