问题描述
如果我有这样的表结构:
If I have a table structure like this:
ProductCode Date
Foo 4/1/2012
Foo 4/2/2012
Foo 4/3/2012
Foo 4/6/2012
Foo 4/7/2012
Foo 4/8/2012
Foo 4/9/2012
Foo 4/10/2012
Foo 4/15/2012
Foo 4/16/2012
Foo 4/17/2012
有没有办法查询给定 ProductCode 和 Date 的日期范围(假设范围必须是连续的)?换句话说，对于这个表，Foo 存在于 3 个日期范围内:4/1-4/3;4/6-4/10;和 4/15-4/17，我正在寻找给定日期的日期范围.
Is there a way to query for the date range for a given ProductCode and Date (assuming that ranges MUST be sequential)? In other words, for this table, Foo exists on 3 date ranges: 4/1-4/3; 4/6-4/10; and 4/15-4/17 and I'm looking for the date range given a date.
请注意 Foo 没有日期的 4/4、4/5、4/11>、4/12、4/13 和 4/14.
Please note that Foo doesn't have date's 4/4, 4/5, 4/11, 4/12, 4/13 and 4/14.
示例:ProductCode=Foo, Date=4/2 将返回 4/1-4/3 因为条目是连续的.ProductCode=Foo, Date=4/4 不会返回任何内容ProductCode=Foo, Date=4/7 将返回 4/6-4/10 因为条目是连续的.ProductCode=Foo, Date=4/12 不会返回任何内容
等
Examples:
ProductCode=Foo, Date=4/2 would return 4/1-4/3 because the entries are sequential.
ProductCode=Foo, Date=4/4 would return nothing
ProductCode=Foo, Date=4/7 would return 4/6-4/10 because the entries are sequential.
ProductCode=Foo, Date=4/12 would return nothing
etc.
推荐答案
本来可以使用 LAG，如果 SQL Server 2005 支持它.不幸的是，LAG 窗口函数仅适用于 SQL Server 2012，并且 PostgreSQL 8.4 及更高版本 ;-)
Could have used LAG, if SQL Server 2005 supported it. Unfortunately LAG window function works on SQL Server 2012 only, and PostgreSQL 8.4 and above ;-)
我认为可以在 SQL Server 2005 上运行，SQLFiddle 不支持 SQL 2005，只尝试了 SQLFiddle 的 SQL Server 2008，而不是 2012:
Works on SQL Server 2005 I supposed, SQLFiddle has no SQL 2005 support, tried SQLFiddle's SQL Server 2008 only, not 2012:
with DetectLeaders as
(
select cr.ProductCode, CurRowDate = cr.Date, PrevRowDate = pr.Date
from tbl cr
left join tbl pr
on pr.ProductCode = cr.ProductCode AND cr.Date = DATEADD(DAY,1,pr.Date)
),
MembersLeaders as
(
select *,
MemberLeader =
(select top 1 CurRowDate
from DetectLeaders nearest
where nearest.PrevRowDate is null
and nearest.ProductCode = DetectLeaders.ProductCode
and DetectLeaders.CurRowDate >= nearest.CurRowDate
order by nearest.CurRowDate desc)
from DetectLeaders
)
select BeginDate = MIN(CurRowDate), EndDate = MAX(CurRowDate)
from MembersLeaders
where MemberLeader =
(select MemberLeader
from MembersLeaders
where ProductCode = 'Foo' and CurRowDate = '4/7/2012')
现场测试:http://sqlfiddle.com/#!3/3fd1f/1
基本上它是这样工作的:
Basically this is how it works:
PRODUCTCODE CURROWDATE PREVROWDATE MEMBERLEADER
Foo 2012-04-01 2012-04-01
Foo 2012-04-02 2012-04-01 2012-04-01
Foo 2012-04-03 2012-04-02 2012-04-01
Foo 2012-04-06 2012-04-06
Foo 2012-04-07 2012-04-06 2012-04-06
Foo 2012-04-08 2012-04-07 2012-04-06
Foo 2012-04-09 2012-04-08 2012-04-06
Foo 2012-04-10 2012-04-09 2012-04-06
Foo 2012-04-15 2012-04-15
Foo 2012-04-16 2012-04-15 2012-04-15
Foo 2012-04-17 2012-04-16 2012-04-15
Bar 2012-05-01 2012-05-01
Bar 2012-05-02 2012-05-01 2012-05-01
Bar 2012-05-03 2012-05-02 2012-05-01
Bar 2012-05-06 2012-05-06
Bar 2012-05-07 2012-05-06 2012-05-06
Bar 2012-05-08 2012-05-07 2012-05-06
Bar 2012-05-09 2012-05-08 2012-05-06
Bar 2012-05-10 2012-05-09 2012-05-06
Bar 2012-05-15 2012-05-15
Bar 2012-05-16 2012-05-15 2012-05-15
Bar 2012-05-17 2012-05-16 2012-05-15
http://sqlfiddle.com/#!3/35818/11
这篇关于SQL Server - 查询最近的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！