如何在SQL中选择连续日期

如何在SQL中选择连续日期

我认为这是 “连续日期分组岛”
问题的一种变体。可以使用

ROW_NUMBER()

以下方法完成：

SQL小提琴

CREATE TABLE Test(    tDate   DATETIME)INSERT INTO Test VALUES('20150311'), ('20150312'), ('20150313'), ('20150316');DECLARE @startDate  DATE = '20150311'DECLARE @endDate    DATE = '20150317';WITH Cte AS(    SELECt        *,        RN = DATEADD(DD, - (ROW_NUMBER() OVER(ORDER BY tDATE) - 1), tDate)    FROM Test    WHERe         tDate >= @startDate        AND tDate < DATEADD(DAY, 1, @endDate))SELECt CAST(tDate AS DATE)FROM CTEWHERe RN = @startDate

结果

|------------|| 2015-03-11 || 2015-03-12 || 2015-03-13 |

---

这是SQL Server 2005版本：

SQL小提琴

DECLARE @startDate  DATETIMEDECLARE @endDate    DATETIMESET @startDate  = '20150311'SET @endDate    = '20150317';WITH Cte AS(    SELECt        *,        RN = DATEADD(DD, -(ROW_NUMBER() OVER(ORDER BY tDATE)-1), tDate)    FROM Test    WHERe         tDate >= @startDate        AND tDate < DATEADD(DAY, 1, @endDate))SELECt ConVERT(VARCHAr(10), tDate, 121)FROM CTEWHERe RN = @startDate