根据起止日期生成连续日期

根据起止日期生成连续的日期，又两种方法，如下：

--- 方法一select ContractRoomID,dateadd(day,number,'2016-07-15'),0 from ContractRoom ccleft join master.dbo.spt_values on   number <=DATEDIFF(day,'2016-07-15','2016-08-15')where cc.ContractRoomID='R150006716C001' and type ='P'

结果：

 --- 方法二，这种方法只能在MSSQL 2008 以上版本中执行, 使用了CTE公共表达式中递归的思想declare  @t  table(id varchar(50) , bizdate date);declare @startDate date = '2016-06-01'; declare @EndDate date =  '2016-08-15' ;with cte0 as (select   'R160016001' as contractRoomId , @startDate as startDate, @EndDate as endDate ), cte1 As (    Select  contractRoomId , startDate As targetDate from cte0     union all    Select contractRoomId , dateadd(day,1,targetDate) From cte1     Where dateadd(day,1,targetDate)<=@EndDate )  insert into @t (id, bizdate)select contractRoomId, targetDate From cte1  ;select  * from @t

结果：

方法2的递归思想，可参考
Actual Practice : [with CTE]&[convert]&[coalesce] as in my work - 4