sql-server - SQL查找最长序列

Question

如果我有一个包含日期的表格，例如(年-月-日然后是时间格式):

2015-06-22 12:39:11.257
2015-06-22 15:44:46.790
2015-06-22 15:48:50.583
2015-06-23 08:25:50.060
2015-07-01 07:11:37.037
2015-07-07 13:40:11.997
2015-07-08 13:12:08.723
2015-07-08 13:12:13.900
2015-07-08 13:12:16.010
2015-07-10 12:29:59.777
2015-07-13 15:42:49.077
2015-07-13 15:47:48.670
2015-07-13 15:47:51.547
2015-07-14 08:11:53.023
2015-07-14 08:14:21.243
2015-07-14 08:16:49.410
2015-07-14 08:17:11.997
2015-07-14 09:58:28.840
2015-07-14 09:59:34.640
2015-07-15 15:39:39.993
2015-07-17 08:45:20.157
2015-07-24 14:00:00.487
2015-07-24 14:03:53.773
2015-07-24 14:12:41.717
2015-07-24 14:13:33.957
2015-07-24 14:15:40.953
2015-08-25 12:43:03.920

...有没有一种方法(在 SQL 中)我可以找到最长的连续天数。我只需要总天数。所以在上面，有 6 月 22 日和 6 月 23 日的条目，所以序列有 2 天。还有 7 月 13 日、7 月 14 日和 7 月 15 日的条目；这是最长的序列 - 3 天。我不关心时间部分，所以在午夜之前进入，然后在午夜之后进入将计为 2 天。

所以我想要一些可以查看表的 SQL，并返回上面的值 3。

Answer 1

不需要游标或任何类型的递归来解决这个问题。您可以使用间隙和孤岛技术来执行此操作。这会从您的示例数据中生成所需的输出。

with SomeDates as
(
    select cast('2015-06-22 12:39:11.257' as datetime) as MyDate union all
    select '2015-06-22 15:44:46.790' union all
    select '2015-06-22 15:48:50.583' union all
    select '2015-06-23 08:25:50.060' union all
    select '2015-07-01 07:11:37.037' union all
    select '2015-07-07 13:40:11.997' union all
    select '2015-07-08 13:12:08.723' union all
    select '2015-07-08 13:12:13.900' union all
    select '2015-07-08 13:12:16.010' union all
    select '2015-07-10 12:29:59.777' union all
    select '2015-07-13 15:42:49.077' union all
    select '2015-07-13 15:47:48.670' union all
    select '2015-07-13 15:47:51.547' union all
    select '2015-07-14 08:11:53.023' union all
    select '2015-07-14 08:14:21.243' union all
    select '2015-07-14 08:16:49.410' union all
    select '2015-07-14 08:17:11.997' union all
    select '2015-07-14 09:58:28.840' union all
    select '2015-07-14 09:59:34.640' union all
    select '2015-07-15 15:39:39.993' union all
    select '2015-07-17 08:45:20.157' union all
    select '2015-07-24 14:00:00.487' union all
    select '2015-07-24 14:03:53.773' union all
    select '2015-07-24 14:12:41.717' union all
    select '2015-07-24 14:13:33.957' union all
    select '2015-07-24 14:15:40.953' union all
    select '2015-08-25 12:43:03.920'
)
, GroupedDates as
(
    select cast(MyDate as DATE) as MyDate
        , DATEADD(day, - ROW_NUMBER() over (Order by cast(MyDate as DATE)), cast(MyDate as DATE)) as DateGroup
    from SomeDates
    group by cast(MyDate as DATE)
)
, SortedDates as
(
    select DATEDIFF(day, min(MyDate), MAX(MyDate)) + 1 as GroupCount
        , min(MyDate) as StartDate
        , MAX(MyDate) as EndDate
    from GroupedDates
    group by DateGroup  
)

select top 1 GroupCount
    , StartDate
    , EndDate
from SortedDates
order by GroupCount desc