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

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

pic

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

pic

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

程式碼如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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 遞迴

話不多說先貼上程式碼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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遞迴原理 :

PIC

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

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

使用CTE遞迴必須使用UNION ALL

最後CTE結果集就會呈現如下^^
https://az787680.vo.msecnd.net/user/九桃/5cb059bd-5868-490a-a5fc-3b8f69aec405/1521377621_11696.PNG

補充 oracle解法

同場加映!!

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

1
2
3
4
5
6
7
8
9
10
11
12
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 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^