使用CTE生成辅助表(数字或时间)等

数字辅助表：

 

declare @start_digital int = 0,
        @end_digital int = 9

;with digital_rangs(digital) 
as
(
    select  @start_digital 
    union all
    select [digital] = [digital] + 1
    from [digital_rangs]
    where [digital] < @end_digital
)

select [digital] from [digital_rangs] option (maxrecursion 0);

 

时间辅助表：

 

 

declare @start_date date = '2019-05-01',
        @end_date date = '2019-05-20'

;with [date_rangs]([date]) 
as
(
    select @start_date 
    union all
    select [date] = dateadd(day,1,[date])
    from [date_rangs]
    where [date] < @end_date
)

select [date] from [date_rangs] option (maxrecursion 0);