从问题理解与活用SQL语法-补充：取得多组日期区间内的所有日期 (CTE 递迴查询)

回想距离上次铁人赛到现在已经快一年了
后来工作经常用到蛮多 CTE 递迴查询
刚好在新的铁人赛开始前另外笔记一下最近遇到蛮好用的需求：取得多组日期区间内的所有日期
可惜找不到方法补充在原系列文

一、最近工作遇到的需求实例

有一个活动资料表，有三个栏位：活动名称、活动开始日、活动结束日
活动页面有一个行事曆，需要标示某个日期区间里，有活动的所有日期

例如活动资料表当中，八月有这些活动：

活动名称活动开始日期活动结束日期test012020-08-052020-08-09test022020-08-072020-08-11test032020-08-142020-08-16

八月期间，所有需要标示有举办活动的日期：

日期2020-08-052020-08-062020-08-072020-08-082020-08-092020-08-102020-08-112020-08-142020-08-152020-08-16

二、SQL

playground

1.表结构与资料

CREATE TABLE Item (  Item_No int PRIMARY KEY IDENTITY ,  ActivityName NVARCHAR(200),  StartDate DATETIME,  EndDate DATETIME,)INSERT INTO Item (ActivityName, StartDate, EndDate)VALUES('test01', '2020-08-05', '2020-08-09'),('test02', '2020-08-07', '2020-08-11'),('test03', '2020-07-29', '2020-08-03'),('test04', '2020-08-29', '2020-09-03'),('test05', '2020-09-10', '2020-09-13'),('test06', '2020-08-14', '2020-08-16')

2.取得八月所有活动，举办的所有日期

WITH dates AS (        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate        FROM Item        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )        UNION ALL        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate        FROM dates        WHERE Date < EndDate) SELECT ActivityName,        CAST([Date] AS DATE) AS [Date],        CAST(StartDate AS DATE) AS StartDate,       CAST(EndDate AS DATE) AS EndDateFROM datesWHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'ORDER BY StartDate, [Date];

ActivityNameDateStartDateEndDatetest032020-08-012020-07-292020-08-03test032020-08-022020-07-292020-08-03test032020-08-032020-07-292020-08-03test012020-08-052020-08-052020-08-09test012020-08-062020-08-052020-08-09test012020-08-072020-08-052020-08-09test012020-08-082020-08-052020-08-09test012020-08-092020-08-052020-08-09test022020-08-072020-08-072020-08-11test022020-08-082020-08-072020-08-11test022020-08-092020-08-072020-08-11test022020-08-102020-08-072020-08-11test022020-08-112020-08-072020-08-11test062020-08-142020-08-142020-08-16test062020-08-152020-08-142020-08-16test062020-08-162020-08-142020-08-16test042020-08-292020-08-292020-09-03test042020-08-302020-08-292020-09-03test042020-08-312020-08-292020-09-03

3. 取得八月所有举办活动的日期

WITH dates AS (        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate        FROM Item        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )        UNION ALL        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate        FROM dates        WHERE Date < EndDate) SELECT DISTINCT CAST([Date] AS DATE) AS [Date]FROM datesWHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'ORDER BY [Date]

Date2020-08-012020-08-022020-08-032020-08-052020-08-062020-08-072020-08-082020-08-092020-08-102020-08-112020-08-142020-08-152020-08-162020-08-292020-08-302020-08-31

三、CTE 递迴查询结构

WITH recursive_sql (column_list)AS(    -- Anchor member    初始化 SELECT ...    UNION ALL    -- Recursive member that references recursive_sql.    递迴查询 SELECT...FROM recursive_sql)-- 呼叫递迴查询SELECT *FROM   recursive_sql

四、说明

第一次呼叫递迴查询时，会先执行上方的初始化 SELECT，取得所有的活动资料接着将初始化 SELECT得到的资料，将活动开始日+1代入递迴查询 SELECT，开始递迴查询直到[Date] = DATEADD(DAY, 1, [Date]) 带入递迴查询 SELECT时，[Date] < EndDate，递迴查询结果为空，结束递迴查询

如果其他大大有更好的写法，欢迎指教与提出建议