问题描述
如果我有这样的表结构:
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