一个有点棘手的 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:按连续记录分组
发布评论