我认为这是 “连续日期分组岛”
问题的一种变体。可以使用
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
