SQL:对连续记录进行分组

Question

一个稍微棘手的 SQL 问题(我们运行的是 SQL Server 2000)。

我有下表，StoreCount -

WeekEndDate    StoreCount
2010-07-25     359
2010-07-18     359
2010-07-11     358
2010-07-04     358
2010-06-27     358
2010-06-20     358
2010-06-13     358
2010-06-06     359
2010-05-30     360
2010-05-23     360
2010-05-16     360

我想将其转换为以下输出 -

StartDate    EndDate       StoreCount
2010-07-18   2010-07-25    359
2010-06-13   2010-07-11    358
2010-06-06   2010-06-06    359
2010-05-16   2010-05-30    360

如您所见，我想仅根据商店数量按顺序一起运行来对它们进行分组。

Answer 1

这里有一个问题，只是它可能有 SS2k 中不可用的语法。它实际上是在 Oracle 上编写的，因为我不再有那个版本的 SS。唯一的问题可能是选择的选择...(自从我使用 SS2k 以来已经有一段时间了，所以很难记住当时哪些功能不可用。)

select min(weekenddate) as start_date, end_date, storecount
from (
select s1.weekenddate
     , (select max(weekenddate)
          from store_stats s2
         where s2.storecount = s1.storecount
           and not exists (select null
                             from store_stats s3
                            where s3.weekenddate < s2.weekenddate
                              and s3.weekenddate > s1.weekenddate
                              and s3.storecount <> s1.storecount)
       ) as end_date
     , s1.storecount
from store_stats s1
) result
group by end_date, storecount
order by 1 desc


START_DATE END_DATE   STORECOUNT
---------- ---------- ----------
2010-07-18 2010-07-25        359
2010-06-13 2010-07-11        358
2010-06-06 2010-06-06        359
2010-05-16 2010-05-30        360