SQL Server中通用資料表運算式（CTE）遞迴的產生協助資料

標籤：

 

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

 

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

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

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

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

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

 

1，產生連續數字（當然數位起始值，間隔值都可以自訂）

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

 

2，CTE遞迴產生連續日期

--產生連續日期;with GenerateHelpDataas(    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 GenerateHelpDataas(    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 GenerateHelpDataas(    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 timeSectionfrom 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 timeSectionfrom GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

 

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

 

 　總結：本文示範了幾種常用的根據CTE遞迴產生協助資料的情況，如果需要協助資料，可以根據CTE的遞迴特性做靈活處理。

 

SQL Server中通用資料表運算式（CTE）遞迴的產生協助資料