SQL:按连续记录分组 [英] SQL: Group By on Consecutive Records

问题描述

一个有点棘手的 SQL 问题(我们运行的是 SQL Server 2000).

A slightly tricky SQL question (we are running SQL server 2000).

我有下表，StoreCount -

I have the following table, 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

我想把它变成下面的输出 -

I want to turn this into the following output -

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

如您所见，我想对商店计数进行分组，仅在它们按顺序一起运行时进行分组.

As you can see, I'm wanting to group the store counts, by only as they run in sequence together.

推荐答案

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

Here's a kick at the can, only it may have syntax not available in SS2k. It was actually written on Oracle as I don't have that version of SS around anymore. The only catch might be the the select of a select...(it's been a while since I've used SS2k, so it's hard to remember what features weren't available back then.)

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

这篇关于SQL:按连续记录分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！