SQL Server中公用表表達式 CTE 遞歸的生成幫助數據

本文出處：http://www.cnblogs.com/wy123/p/5960825.html

我們在做開發的時候，有時候會需要一些幫助數據，必須需要連續的數字，連續間隔的時間點，連續的季度日期等等
常見很多人利用master庫的spt_values系統表，這個當然沒有問題

比如下麵這個（沒截完，結果是0-2047）

這樣也可以使用，但是感覺不夠靈活，一是不是隨便一個賬號都可以訪問master資料庫的，而是他這裡面也只有這麼一個連續的數字了，
想要別的結果集就不太弄了，
類似數據可以用公用表表達式CTE的遞歸來生成
比如上述的0-2047的結果集

;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

可以直接讓CTE參數邏輯運算，也可以生成臨時表，達到多次重用的目的，這樣感覺是不是也很清爽？

1，生成連續數字（當然數字的起始值，間隔值都可以自定義）

--生成連續數字
;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

2，CTE遞歸生成連續日期

--生成連續日期
;with GenerateHelpData
as
(
    select cast('2016-10-01' as date) as [Date]
    union all
    select DATEADD(D,1,[Date]) from GenerateHelpData where [Date]<'2017-01-01'
)
select [Date] from GenerateHelpData;

3，生成連續間隔的時間點

　　有時候一些統計需要按照一個小時或者半個小時之類的時間間隔做組合，比如統計某天內沒半個小時的小時數據等等

--生成連續間隔的時間點
;with GenerateHelpData
as
(
    select 1 as id, cast('00:00:00' as time(0)) as timeSection
    union all
    select id+1 as id,  cast(dateadd(mi,30,timeSection) as time(0)) as timeSection 
     from GenerateHelpData  where id<49
)
select * from GenerateHelpData

當然這裡就可以非常靈活了，更騷一點的變形

--更騷一點的變形
;with GenerateHelpData
as
(
    select 1 as id, cast('00:00:00' as time(0)) as timeSection
    union all
    select id+1 as id,  cast(dateadd(mi,30,timeSection) as time(0)) as timeSection 
     from GenerateHelpData  where id<49
)
select 
A.timeSection as timeSectionFrom,
B.timeSection as timeSectionTo,
cast(A.timeSection as varchar(10))+'~'+cast(B.timeSection as varchar(10)) as timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

　　4，生成連續季度的最後一天

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select * from GenerateHelpData

通過變形可以生成兩個日期間隔之間的的數據

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 1 as id ,
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     id+1 as id,
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select 
A.EndingDate as DateFrom,
B.EndingDate as DateTo,
cast(A.EndingDate as varchar(10))+'~'+cast(B.EndingDate as varchar(10)) as timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

需要註意的是，CTE遞歸的預設次數是100，如果不指定遞歸次數（option (maxrecursion N);），超出預設最大遞歸次數之後會報錯。

 　總結：本文演示了幾種常用的根據CTE遞歸生成幫助數據的情況，如果需要幫助數據，可以根據CTE的遞歸特性做靈活處理。