sql - 在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 来说，它是 3 (32, 42, 76)。 对于 RC，它是 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

Live example at 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

Live example at SQL Fiddle.