【MySQL】指定年月の初日から最終日までのリストを取得する SQL

SQL で存在するレコードが取得できます。存在しないレコードは穴埋めされません。

そうしますと、とある年月の一日から月末までレコードを取得したいのに、存在しないために歯抜けになってしまった、そんなケースに遭遇しました＞＜。

歯抜けをなくして、存在しないレコードも埋めたいのです。

今回は、指定年月の一日から末日までのリストを返す MySQL をノートいたします。

2016年8月14日18時00分追記。シンプル・日付特化版を投稿いたしました♪

• 【MySQL】指定年月の初日から最終日までのリストを取得する SQL、その2 – oki2a24

一日から末日までのリストを返す MySQL

SET @target = STR_TO_DATE('201601', '%Y%m');
SET @firdt_day = DATE_FORMAT(@target, '%Y-%m-01');
SELECT DATE_FORMAT(DATE_ADD(@firdt_day, INTERVAL tmp.generate_series DAY), '%Y-%m-%d') AS day
FROM
(
    SELECT 0 generate_series FROM DUAL WHERE (@num:=-1)*0 UNION ALL
    SELECT @num:=@num+1 FROM `information_schema`.COLUMNS
) AS tmp
HAVING DATE_FORMAT(day, '%Y%m') = DATE_FORMAT(@target, '%Y%m');

解説

# 変数を使用
SET @target_date = '2016-01-07';
SELECT @target_date;

• MySQLのユーザー変数 – Qiita

# 文字列から日付型を生成
SELECT STR_TO_DATE('01,4,2016', '%d,%m,%Y');

• STR_TO_DATE MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

# 文字列を数値に変換
SELECT CAST('1' AS SIGNED);

# 今月月初
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');

• MySQL 1ヶ月前の月末・月初の日付を取得する ｜ Notebook –

# 今月日数
SELECT DATE_FORMAT(LAST_DAY(NOW()),  '%d')

• MySQL 1ヶ月前の月末・月初の日付を取得する ｜ Notebook –

# 0 から 7 つ連番を作成
SELECT 0 generate_series FROM DUAL WHERE (@num:=-1)*0 UNION ALL
SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7

• 裏MySQLクエリー入門(15) 応用編3 MySQLで連番の仮想表を作成 – イノベートな非日常

# 2015-03-31 から 7 日間を表示
SELECT DATE_FORMAT(DATE_ADD('2015-03-31', INTERVAL tmp.generate_series DAY), '%Y-%m-%d') AS days
FROM
(
    SELECT 0 generate_series FROM DUAL WHERE (@num:=-1)*0 UNION ALL
    SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7
) AS tmp

• MySQL クエリで連続した日付の仮想表を作成する – Symfoware

トラブルで挫折

SET @target = STR_TO_DATE('201601', '%Y%m');
SET @firdt_day = DATE_FORMAT(@target, '%Y-%m-01');
SET @day_num = CAST(DATE_FORMAT(LAST_DAY(@target), '%d') AS SIGNED);
SELECT DATE_FORMAT(DATE_ADD(@firdt_day, INTERVAL tmp.generate_series DAY), '%Y-%m-%d') AS day
FROM
(
    SELECT 0 generate_series FROM DUAL WHERE (@num:=-1)*0 UNION ALL
    SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT @day_num
) AS tmp

LIMIT @day_num でエラー発生＞＜。

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@day_num ) AS tmp’ at line 5

LIMIT には変数を指定できないようですの＞＜。

LIMIT を使用しないかわりに、WHERE、HAVING、で絞り込むようにすることで解決できました♪

• MySQLで今月のデータで絞り込む | hacknote
  [mysql 今月のデータ] で検索
• 【MYSQL】asでリネームしてwhereで使う – MySQL 解決済 | 教えて！goo
  [mysql AS where] で検索

おわりに

一日から末日までのリストを返す MySQL と、別のテーブルの歯抜けのある SQL を結合すれば穴埋めすることができますわね♪

ただ、複雑な SQL となってしまいました＞＜。ですのでせっかく作ったのですけれどと、出番はないかもしれません。。。

PosrgreSQL でしたら GENERATE_SERIES 関数を使って連番を作る SQL はもっとシンプルに作れるそうなのですけれども。

• SQLで，テーブルに存在しない複数行のデータを取得する方法　（PostgreSQLの generate_series関数をマスターしよう） – 主に言語とシステム開発に関して

以上です。