(SQL Server) CTE RECURSIVE (遞迴)製作月曆

如果要製作月報…但只有給起訖日

要產生出如下的列表要怎麼辦…

第一個想到的解法會使用 WHILE + [暫存表]迴圈遍歷把每個月新增入暫存表中

程式碼如下:

DECLARE  @t TABLE
(
    StartDate DATETIME,
	EndDate DATETIME
);

INSERT INTO @t
        ( StartDate, EndDate )
VALUES  ( '2017/01/01', -- StartDate - datetime
          '2018/01/01'  -- EndDate - datetime
          );

--宣告一個起始時間變數
DECLARE @TempStartDate DATETIME
DECLARE @TempEndDate DATETIME

--設置變數 最小時間(起始時間)  和 最大時間
SELECT @TempStartDate = StartDate,@TempEndDate=EndDate 
FROM @t

CREATE TABLE #TEMP(Dates DATETIME)

WHILE(@TempStartDate < @TempEndDate)
BEGIN
     --將資料新增入暫存表
     INSERT INTO #TEMP (Dates) VALUES (@TempStartDate)
     --每跑一次迴圈就加一個月
	 SELECT @TempStartDate = DATEADD(MONTH,1,@TempStartDate)
END 

SELECT * FROM #TEMP

DROP TABLE #TEMP

但這個解法雖然簡單..但程式碼又臭又長..

Q: 有沒有更好看的解法又可達成目的呢?

ANS: 有!! 就是本次主角 CTE 遞迴

話不多說先貼上程式碼

DECLARE  @t TABLE
(
    StartDate DATETIME,
	EndDate DATETIME
);

INSERT  INTO  @t
        ( StartDate, EndDate )
VALUES  ( '2017/01/01', -- StartDate - datetime
          '2018/01/01'  -- EndDate - datetime
          );

;WITH CTE (Dates,EndDate) AS
(
	SELECT StartDate AS Dates,EndDate AS EndDate
	FROM @t
	UNION ALL --注意這邊使用 UNION ALL
	SELECT DATEADD(MONTH,1,Dates),EndDate
	FROM CTE 
	WHERE DATEADD(MONTH,1,Dates) < EndDate --判斷是否目前遞迴月份小於結束日期
)

SELECT CTE.Dates
FROM CTE

接下來解說 CTE遞迴原理 :

可看到CTE中最主要執行四個步驟

取得初始結果集並(錨點結果集) T(0)
將T(0)結果集進行判斷是否滿足 DATEADD(MONTH,1,Dates) < EndDate 不滿足繼續走,並產生T(1)結果集,依照此結果集繼續往下執行
在執行上面的2步驟直到滿足條件 T(0),T(1)…..T(n)
傳回結果集。將之前所有產生結果集 UNION ALL。

使用CTE遞迴必須使用UNION ALL

最後CTE結果集就會呈現如下^^

補充 oracle解法

同場加映!!

如果使用 oracle 可使用 connect by 很簡便取得日曆

CREATE TABLE T
(
    StartDate DATE,
	EndDate DATE
);

INSERT INTO T( StartDate, EndDate ) VALUES  (date '2017-01-01',date '2018-01-01');

select add_months(trunc(StartDate,'mm'),level - 1 ) "Date"
   from T 
connect by trunc(EndDate,'mm') >= add_months(trunc(StartDate,'mm'),level)
  order by 1

http://sqlfiddle.com/#!4/75cd9/14

此文作者：Daniel Shih(石頭)
此文地址： https://isdaniel.github.io/cte-recursive/
版權聲明：本博客所有文章除特別聲明外，均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處！