SQL Server中公用表表達式 CTE 遞歸的生成幫助資料，以及遞歸的典型應用

原文: 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);），超出預設最大遞歸次數之後會報錯。

 ——————————————遞歸原本很容易使用，本文原本是說用遞歸生成幫助資料的，有朋友問到遞歸本身的使用，那就再補充一個DEMO吧———————————————————

補充園友的一個實際應用-20161119

測試資料：

create table ProuctInfo
(
    Id INT,
    ParentId INT,
    ProuctName VARCHAR(50)
)

INSERT INTO ProuctInfo VALUES (1,0,'鏡片')
INSERT INTO ProuctInfo VALUES (2,0,'鏡架')
INSERT INTO ProuctInfo VALUES (101,1,'進階鏡片')
INSERT INTO ProuctInfo VALUES (102,1,'普通鏡片')
INSERT INTO ProuctInfo VALUES (201,2,'進階鏡架')
INSERT INTO ProuctInfo VALUES (202,2,'普通鏡架')

INSERT INTO ProuctInfo VALUES (1001,101,'進階鏡片1')
INSERT INTO ProuctInfo VALUES (1002,102,'普通鏡片2')
INSERT INTO ProuctInfo VALUES (2001,201,'進階鏡架1')
INSERT INTO ProuctInfo VALUES (2002,202,'普通鏡架2')      

原始資料的樣子，很普通

建立一個函數，擷取目前節點的父節點資訊

CREATE FUNCTION dbo.FnGetParentInfo(@id int)
returns varchar(max)
as
begin

declare @name varchar(max)

--查詢某一個節點的所有父節點
;with SubTab
as
(
    select [ID],[ParentID], cast(ProuctName as varchar(200)) as ProuctName
    from ProuctInfo WHERE Id = @id
    union all
    select a.[ID],a.[ParentID],cast(a.ProuctName+'--->'+b.ProuctName as varchar(200)) as ProuctName
    from ProuctInfo a,SubTab b
    where a.[ID]=b.[ParentID] 
)
select @name = ProuctName from SubTab  where ParentId = 0 

return @name
end      

實際效果：

　　

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