iT邦幫忙

5

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

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

pic

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

pic

第一個想到的解法 會使用 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遞迴原理 :

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

此文同步發布在 : 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


2 則留言

1
一級屠豬士
iT邦新手 3 級 ‧ 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)
dog830228 iT邦研究生 4 級‧ 2018-04-09 09:55:49 檢舉

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

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

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

  1. 用您所提的 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'));
  1. 用 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)
  );
  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')));
dog830228 iT邦研究生 4 級‧ 2018-04-12 08:57:45 檢舉

感謝大大補充~

我要留言

立即登入留言