問題描述
如果我有這樣的表結構:
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 - 查詢最近的日期范圍的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！