SQLでカレンダーを取得する (WITH CTE) [SQLServer, MySQL]

SQLでカレンダーのデータを取得する方法を紹介します。
1か月の日付が7日ごとに改行して表示されるカレンダー形式のデータは、プログラムで作成するのも少し手間なので、SQLで取得する方法を覚えておくのもいいかもしれませんよ。

1 1か月のデータを取得する
- 1.1 SQLServerでの取得
- 1.2 MySQLでの取得
2 カレンダーのデータを取得する
- 2.1 SQLServerでの取得
- 2.2 MySQLでの取得

1か月のデータを取得する

カレンダーデータの取得の前に、まずは1か月分のデータの取得をします。
1か月のデータは月初めの日付（1日）～月終わりの日付（末日）の日付を取得します。
WHILEループやFETCHでカーソルを操作する方法が考えられますが、ここではWITH句を利用して再帰的にデータを取得します。

SQLServerでの取得

SQLServerで1か月分のデータを取得するSQLは次のようになります。
以下の例では2020年5月のデータを取得しています。

-- 1か月分のデータを取得する

DECLARE @YEAR INT;
DECLARE @MONTH INT;
-- 1か月分のデータを取得する年月
SET @YEAR = 2020;
SET @MONTH = 5;

DECLARE @START_DATE DATE;
DECLARE @END_DATE DATE;
-- 開始日
SET @START_DATE = DATEFROMPARTS(@YEAR, @MONTH, 1);
-- 終了日
SET @END_DATE = EOMONTH(@START_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)
WITH DATE_TABLE(DATE_VALUE) AS ( 
    SELECT
        @START_DATE
    UNION ALL 
    SELECT
        DATEADD(dd, 1, DATE_VALUE) 
    FROM
        DATE_TABLE 
    WHERE
        DATE_VALUE < @END_DATE
)
SELECT DATE_VALUE FROM DATE_TABLE;

-- 1か月分のデータを取得する

DECLARE @YEAR INT;

DECLARE @MONTH INT;

-- 1か月分のデータを取得する年月

SET @YEAR = 2020;

SET @MONTH = 5;

DECLARE @START_DATE DATE;

DECLARE @END_DATE DATE;

-- 開始日

SET @START_DATE = DATEFROMPARTS(@YEAR, @MONTH, 1);

-- 終了日

SET @END_DATE = EOMONTH(@START_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)

WITH DATE_TABLE(DATE_VALUE) AS (

SELECT

@START_DATE

UNION ALL

SELECT

DATEADD(dd, 1, DATE_VALUE)

FROM

DATE_TABLE

WHERE

DATE_VALUE < @END_DATE

)

SELECT DATE_VALUE FROM DATE_TABLE;

共通テーブル式のWITH句にまず開始のSELECT句を記載して開始日の日付を取得します。（18～19行目）
続けてUNIONで開始日～終了日の前日までの日付に1を加算するSELECT句を記述して日付を取得します。（20～26行目）
こうすることで再帰的に処理され、1か月分の日付を取得することができます。

取得結果

DATE_VALUE
2020-05-01
2020-05-02
2020-05-03
2020-05-04
2020-05-05
2020-05-06
2020-05-07
2020-05-08
2020-05-09
2020-05-10
2020-05-11
2020-05-12
2020-05-13
2020-05-14
2020-05-15
2020-05-16
2020-05-17
2020-05-18
2020-05-19
2020-05-20
2020-05-21
2020-05-22
2020-05-23
2020-05-24
2020-05-25
2020-05-26
2020-05-27
2020-05-28
2020-05-29
2020-05-30
2020-05-31

MySQLでの取得

MySQLで1か月分のデータを取得するSQLは次のようになります。

-- 1か月分のデータを取得する

-- 1か月分のデータを取得する年月
SET @YEAR = 2020;
SET @MONTH = 5;

-- 開始日
SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-1'), DATE);
-- 終了日
SET @END_DATE = LAST_DAY(@START_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)
WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS ( 
    SELECT
        @START_DATE
    UNION ALL 
    SELECT
        DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)
    FROM
        DATE_TABLE 
    WHERE
        DATE_VALUE < @END_DATE
)
SELECT DATE_VALUE FROM DATE_TABLE;

-- 1か月分のデータを取得する

-- 1か月分のデータを取得する年月

SET @YEAR = 2020;

SET @MONTH = 5;

-- 開始日

SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-1'), DATE);

-- 終了日

SET @END_DATE = LAST_DAY(@START_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)

WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS (

SELECT

@START_DATE

UNION ALL

SELECT

DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)

FROM

DATE_TABLE

WHERE

DATE_VALUE < @END_DATE

)

SELECT DATE_VALUE FROM DATE_TABLE;

MySQLの場合もSQLServerの時とほぼ同じですが、MySQLのWITH句で再帰的に処理を行う場合はWITHの後に「RECURSIVE」が必要になります。（13行目）

カレンダーのデータを取得する

ここからが本題のカレンダーデータの取得です。
カレンダーのデータは単純に1か月分のデータではなく、第1週目には、1日になる前の前月の日付が含まれます。また、最終週には、週が終わるまで（土曜日まで）の翌月の日付が含まれます。ですので、第1週の開始日と最終週の終了日を取得してWITH句に指定します。

SQLServerでの取得

SQLServerでカレンダーデータを取得するSQLは次のようになります。

-- 1か月のカレンダーデータを取得する

DECLARE @YEAR INT;
DECLARE @MONTH INT;
-- 1か月のカレンダーデータを取得する年月
SET @YEAR = 2020;
SET @MONTH = 5;

DECLARE @START_DATE DATE;
DECLARE @END_DATE DATE;
-- 開始日
SET @START_DATE = DATEFROMPARTS(@YEAR, @MONTH, 1);
-- 終了日
SET @END_DATE = EOMONTH(@START_DATE);

-- 第１週の開始日
DECLARE @FIRST_WEEK_START_DATE DATE
SET @FIRST_WEEK_START_DATE = DATEADD(DD, ((DATEPART(DW, @START_DATE)) - 1) * -1, @START_DATE);
-- 最終週の終了日
DECLARE @LAST_WEEK_END_DATE DATE
SET @LAST_WEEK_END_DATE = DATEADD(DD, (7 - (DATEPART(DW, @END_DATE))), @END_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)
WITH DATE_TABLE(DATE_VALUE) AS ( 
    SELECT
        @FIRST_WEEK_START_DATE
    UNION ALL 
    SELECT
        DATEADD(dd, 1, DATE_VALUE)
    FROM
        DATE_TABLE 
    WHERE
        DATE_VALUE < @LAST_WEEK_END_DATE
)
SELECT
    WEEK_NO,
    -- MAXを指定することでWEEK_NOごとの各列の値を1レコードに凝縮
    MAX(DATE_1) AS SUNDAY,
    MAX(DATE_2) AS MONDAY,
    MAX(DATE_3) AS TUESDAY,
    MAX(DATE_4) AS WEDNESDAY,
    MAX(DATE_5) AS THURSDAY,
    MAX(DATE_6) AS FRIDAY,
    MAX(DATE_7) AS SATURDAY
FROM
    (
    SELECT
        -- DATEDIFFで日付の差から週番号を取得
        (DATEDIFF(dd, @FIRST_WEEK_START_DATE, DATE_VALUE) / 7) + 1 AS WEEK_NO,
        -- CASE式で曜日ごとに行を列に展開
        CASE WHEN DATEPART(dw, DATE_VALUE) = 1 THEN DATE_VALUE ELSE NULL END AS DATE_1,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 2 THEN DATE_VALUE ELSE NULL END AS DATE_2,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 3 THEN DATE_VALUE ELSE NULL END AS DATE_3,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 4 THEN DATE_VALUE ELSE NULL END AS DATE_4,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 5 THEN DATE_VALUE ELSE NULL END AS DATE_5,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 6 THEN DATE_VALUE ELSE NULL END AS DATE_6,
        CASE WHEN DATEPART(dw, DATE_VALUE) = 7 THEN DATE_VALUE ELSE NULL END AS DATE_7
    FROM
        DATE_TABLE
    ) AS DATE_TABLE
GROUP BY
    WEEK_NO
ORDER BY
    WEEK_NO;

-- 1か月のカレンダーデータを取得する

DECLARE @YEAR INT;

DECLARE @MONTH INT;

-- 1か月のカレンダーデータを取得する年月

SET @YEAR = 2020;

SET @MONTH = 5;

DECLARE @START_DATE DATE;

DECLARE @END_DATE DATE;

-- 開始日

SET @START_DATE = DATEFROMPARTS(@YEAR, @MONTH, 1);

-- 終了日

SET @END_DATE = EOMONTH(@START_DATE);

-- 第１週の開始日

DECLARE @FIRST_WEEK_START_DATE DATE

SET @FIRST_WEEK_START_DATE = DATEADD(DD, ((DATEPART(DW, @START_DATE)) - 1) * -1, @START_DATE);

-- 最終週の終了日

DECLARE @LAST_WEEK_END_DATE DATE

SET @LAST_WEEK_END_DATE = DATEADD(DD, (7 - (DATEPART(DW, @END_DATE))), @END_DATE);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)

WITH DATE_TABLE(DATE_VALUE) AS (

SELECT

@FIRST_WEEK_START_DATE

UNION ALL

SELECT

DATEADD(dd, 1, DATE_VALUE)

FROM

DATE_TABLE

WHERE

DATE_VALUE < @LAST_WEEK_END_DATE

)

SELECT

WEEK_NO,

-- MAXを指定することでWEEK_NOごとの各列の値を1レコードに凝縮

MAX(DATE_1) AS SUNDAY,

MAX(DATE_2) AS MONDAY,

MAX(DATE_3) AS TUESDAY,

MAX(DATE_4) AS WEDNESDAY,

MAX(DATE_5) AS THURSDAY,

MAX(DATE_6) AS FRIDAY,

MAX(DATE_7) AS SATURDAY

FROM

(

SELECT

-- DATEDIFFで日付の差から週番号を取得

(DATEDIFF(dd, @FIRST_WEEK_START_DATE, DATE_VALUE) / 7) + 1 AS WEEK_NO,

-- CASE式で曜日ごとに行を列に展開

CASE WHEN DATEPART(dw, DATE_VALUE) = 1 THEN DATE_VALUE ELSE NULL END AS DATE_1,

CASE WHEN DATEPART(dw, DATE_VALUE) = 2 THEN DATE_VALUE ELSE NULL END AS DATE_2,

CASE WHEN DATEPART(dw, DATE_VALUE) = 3 THEN DATE_VALUE ELSE NULL END AS DATE_3,

CASE WHEN DATEPART(dw, DATE_VALUE) = 4 THEN DATE_VALUE ELSE NULL END AS DATE_4,

CASE WHEN DATEPART(dw, DATE_VALUE) = 5 THEN DATE_VALUE ELSE NULL END AS DATE_5,

CASE WHEN DATEPART(dw, DATE_VALUE) = 6 THEN DATE_VALUE ELSE NULL END AS DATE_6,

CASE WHEN DATEPART(dw, DATE_VALUE) = 7 THEN DATE_VALUE ELSE NULL END AS DATE_7

FROM

DATE_TABLE

) AS DATE_TABLE

GROUP BY

WEEK_NO

ORDER BY

WEEK_NO;

実行結果

データを取得するSQL（WITH句の後のSELECT句）では、WITH句で定義したDATE_TABLEからデータを取得する際に、CASE式で曜日番号ごとに横に展開します。（51～57行目）
展開した結果をサブクエリにしてMAX関数で最大値を取得することでWEEK_NO（週番号）ごとに1レコードになるようにします。（週番号が該当しない列の値はNULLになるのでMAX関数で最大値を取得することで有効な値のみ残り、結果として1行になります。）（38～44行目）

取得したデータを列に展開する方法については以下の記事も参考にしてください。

MySQLでの取得

MySQLでカレンダーデータを取得するSQLは次のようになります。

-- 1か月のカレンダーデータを取得する

-- 1か月のカレンダーデータを取得する年月
SET @YEAR = 2020;
SET @MONTH = 5;

-- 開始日
SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-1'), DATE);
-- 終了日
SET @END_DATE = LAST_DAY(@START_DATE);

-- 第１週の開始日
SET @FIRST_WEEK_START_DATE = DATE_ADD(@START_DATE, INTERVAL ((DAYOFWEEK(@START_DATE) - 1) * -1) DAY);
-- 最終週の終了日
SET @LAST_WEEK_END_DATE = DATE_ADD(@END_DATE, INTERVAL (7 - (DAYOFWEEK(@END_DATE))) DAY);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)
WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS ( 
    SELECT
        @FIRST_WEEK_START_DATE
    UNION ALL 
    SELECT
        DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)
    FROM
        DATE_TABLE 
    WHERE
        DATE_VALUE < @LAST_WEEK_END_DATE
)
SELECT
    WEEK_NO,
    -- MAXを指定することでWEEK_NOごとの各列の値を1レコードに凝縮
    MAX(DATE_1) AS SUNDAY,
    MAX(DATE_2) AS MONDAY,
    MAX(DATE_3) AS TUESDAY,
    MAX(DATE_4) AS WEDNESDAY,
    MAX(DATE_5) AS THURSDAY,
    MAX(DATE_6) AS FRIDAY,
    MAX(DATE_7) AS SATURDAY
FROM
    (
    SELECT
        -- DATEDIFFで日付の差から週番号を取得
        FLOOR(DATEDIFF(DATE_VALUE, @FIRST_WEEK_START_DATE) / 7) + 1 AS WEEK_NO,
        -- CASE式で曜日ごとに行を列に展開
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE ELSE NULL END AS DATE_1,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE ELSE NULL END AS DATE_2,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE ELSE NULL END AS DATE_3,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE ELSE NULL END AS DATE_4,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE ELSE NULL END AS DATE_5,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE ELSE NULL END AS DATE_6,
        CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE ELSE NULL END AS DATE_7
    FROM
        DATE_TABLE
    ) AS DATE_TABLE
GROUP BY
    WEEK_NO
ORDER BY
    WEEK_NO;

-- 1か月のカレンダーデータを取得する

-- 1か月のカレンダーデータを取得する年月

SET @YEAR = 2020;

SET @MONTH = 5;

-- 開始日

SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-1'), DATE);

-- 終了日

SET @END_DATE = LAST_DAY(@START_DATE);

-- 第１週の開始日

SET @FIRST_WEEK_START_DATE = DATE_ADD(@START_DATE, INTERVAL ((DAYOFWEEK(@START_DATE) - 1) * -1) DAY);

-- 最終週の終了日

SET @LAST_WEEK_END_DATE = DATE_ADD(@END_DATE, INTERVAL (7 - (DAYOFWEEK(@END_DATE))) DAY);

-- 日付一覧を作成する共通テーブル式: CTE(Common Table Expression)

WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS (

SELECT

@FIRST_WEEK_START_DATE

UNION ALL

SELECT

DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)

FROM

DATE_TABLE

WHERE

DATE_VALUE < @LAST_WEEK_END_DATE

)

SELECT

WEEK_NO,

-- MAXを指定することでWEEK_NOごとの各列の値を1レコードに凝縮

MAX(DATE_1) AS SUNDAY,

MAX(DATE_2) AS MONDAY,

MAX(DATE_3) AS TUESDAY,

MAX(DATE_4) AS WEDNESDAY,

MAX(DATE_5) AS THURSDAY,

MAX(DATE_6) AS FRIDAY,

MAX(DATE_7) AS SATURDAY

FROM

(

SELECT

-- DATEDIFFで日付の差から週番号を取得

FLOOR(DATEDIFF(DATE_VALUE, @FIRST_WEEK_START_DATE) / 7) + 1 AS WEEK_NO,

-- CASE式で曜日ごとに行を列に展開

CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE ELSE NULL END AS DATE_1,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE ELSE NULL END AS DATE_2,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE ELSE NULL END AS DATE_3,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE ELSE NULL END AS DATE_4,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE ELSE NULL END AS DATE_5,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE ELSE NULL END AS DATE_6,

CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE ELSE NULL END AS DATE_7

FROM

DATE_TABLE

) AS DATE_TABLE

GROUP BY

WEEK_NO

ORDER BY

WEEK_NO;

実行結果

MySQLでのカレンダーデータの取得もSQLServerの場合とほぼ同じです。
月の最終日の取得関数がEOMONTH関数からLAST_DAYになり、曜日の番号を取得する関数がDATEPART関数からDAYOFWEEK関数になります。
また、日付を加算するDATEADD関数がDATE_ADD関数になり、DATEDIFF関数では引数の日付が逆になります。