我认为这有点不同对连续日期的岛屿进行分组 http://www.sqlservercentral.com/articles/T-SQL/71550/问题。这可以使用以下方法完成ROW_NUMBER()
:
SQL小提琴 http://sqlfiddle.com/#!6/c67a0/6/0
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 CTE
WHERE RN = @startDate
RESULT
|------------|
| 2015-03-11 |
| 2015-03-12 |
| 2015-03-13 |
这是 SQL Server 2005 版本:
SQL小提琴 http://sqlfiddle.com/#!6/17eaa/2/0
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @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 CTE
WHERE RN = @startDate