SQL:按连续记录分组

本文介绍了SQL:按连续记录分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

一个有点棘手的 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