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

sql tsql mssql cte union

石頭 2018-04-08 20:39:57 ‧ 23502 瀏覽

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

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

第一個想到的解法會使用 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結果集就會呈現如下^^

此文同步發布在 : https://dotblogs.com.tw/daniel/2018/03/18/213231

補充 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

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 則留言

一級屠豬士

iT邦新手 2 級 ‧ 2018-04-08 23:31:10

用Postgresql 輕鬆愉快多了.

select dates
  from generate_series('2017-01-01 00:00:00'::timestamp,
                       '2017-12-01 00:00:00'::timestamp,
                       '1 months') as gs(dates);

        dates        
---------------------
 2017-01-01 00:00:00
 2017-02-01 00:00:00
 2017-03-01 00:00:00
 2017-04-01 00:00:00
 2017-05-01 00:00:00
 2017-06-01 00:00:00
 2017-07-01 00:00:00
 2017-08-01 00:00:00
 2017-09-01 00:00:00
 2017-10-01 00:00:00
 2017-11-01 00:00:00
 2017-12-01 00:00:00
(12 rows)

回應 1
檢舉

石頭 iT邦高手 1 級 ‧ 2018-04-09 09:55:49 檢舉

Postgresql 用起來真的很簡便謝謝分享

登入發表回應

Arsene

iT邦新手 5 級 ‧ 2018-04-11 10:08:02

Oracle有幾個方法比較少見
分享如下
供參考

用您所提的 Connect by

 SELECT ADD_MONTHS(TO_DATE('2017/01/01', 'YYYY/MM/DD'), rownum - 1)
   FROM dual
 CONNECT BY rownum <=
            MONTHS_BETWEEN(TO_DATE('2018/01/01', 'YYYY/MM/DD'),
                           TO_DATE('2017/01/01', 'YYYY/MM/DD'));

用 Model with iterate

 SELECT DATES FROM (SELECT TO_DATE('2017/01/01', 'YYYY/MM/DD') DT FROM DUAL)
 MODEL 
  DIMENSION BY (0 D) MEASURES (DT DATES)
  RULES ITERATE (1000) UNTIL (DATES[ITERATION_NUMBER+1]>= ADD_MONTHS(TO_DATE('2018/01/01', 'YYYY/MM/DD'),-1)) 
  (
  DATES[ITERATION_NUMBER+1] = ADD_MONTHS(DATES[ITERATION_NUMBER],1)
  );

用PL/SQL,搭配 Pipeline

CREATE OR REPLACE PACKAGE XX_PIPL_PKG AS
  TYPE DATE_TBL_TYPE IS TABLE OF DATE;
  FUNCTION DATE_PIPL(V_START_DATE DATE, V_END_DATE DATE) RETURN DATE_TBL_TYPE
    PIPELINED;
END XX_PIPL_PKG;

CREATE OR REPLACE PACKAGE BODY XX_PIPL_PKG AS
  FUNCTION DATE_PIPL(V_START_DATE DATE, V_END_DATE DATE) RETURN DATE_TBL_TYPE
    PIPELINED IS
  BEGIN
    FOR i IN 1 .. MONTHS_BETWEEN(V_END_DATE, V_START_DATE) LOOP
      PIPE row(ADD_MONTHS(V_START_DATE, i - 1));
    END LOOP;
    RETURN;
  END;
END XX_PIPL_PKG;

SELECT COLUMN_VALUE AS PERIOD_CODE
  FROM TABLE(XX_PIPL_PKG.DATE_PIPL(TO_DATE('2017/01/01', 'YYYY/MM/DD'),
                                   TO_DATE('2018/01/01', 'YYYY/MM/DD')));