カテゴリー
コンピューター

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

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

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

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

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

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

一日から末日までのリストを返す 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;
# 文字列から日付型を生成
SELECT STR_TO_DATE('01,4,2016', '%d,%m,%Y');
# 文字列を数値に変換
SELECT CAST('1' AS SIGNED);
# 今月月初
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');
# 今月日数
SELECT DATE_FORMAT(LAST_DAY(NOW()),  '%d')
# 0 から 7 つ連番を作成
SELECT 0 generate_series FROM DUAL WHERE (@num:=-1)*0 UNION ALL
SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7
# 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

トラブルで挫折

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 と、別のテーブルの歯抜けのある SQL を結合すれば穴埋めすることができますわね♪

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

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

以上です。

「【MySQL】指定年月の初日から最終日までのリストを取得する SQL」への1件の返信

こんにちは。【一日から末日までのリストを返す MySQL と、別のテーブルの歯抜けのある SQL を結合すれば穴埋めすることができますわね♪】まさしくコレがしたくてこちらにたどり着きました。有益な情報有難うございます。使わせて頂きます。本当に有難うございました。

コメントを残す