日付データの仮想表を作る方法です。実テーブルがなくても「今日から10日間の日付を表示」などすることができます。
目次
今日から10日間の日付を表示
mysql> SELECT date_format(date_add(CURDATE(), interval tmp.generate_series - 1 day), '%Y-%m-%d') as 日付
-> FROM (SELECT 0 generate_series
-> FROM DUAL
-> WHERE (@num:=1-1)*0
-> UNION ALL
-> SELECT @num:=@num+1
-> FROM `information_schema`.COLUMNS
-> LIMIT 10) as tmp;
+------------+
| 日付 |
+------------+
| 2018-11-24 |
| 2018-11-25 |
| 2018-11-26 |
| 2018-11-27 |
| 2018-11-28 |
| 2018-11-29 |
| 2018-11-30 |
| 2018-12-01 |
| 2018-12-02 |
| 2018-12-03 |
+------------+
10 rows in set (0.07 sec)曜日も追加で表示
mysql> SELECT date_format(date_add(CURDATE(), interval tmp.generate_series - 1 day), '%Y-%m-%d') as 日付,
-> CASE dayofweek(date_format(date_add(CURDATE(), interval tmp.generate_series - 1 day), '%Y-%m-%d'))
-> WHEN 1 THEN '日'
-> WHEN 2 THEN '月'
-> WHEN 3 THEN '火'
-> WHEN 4 THEN '水'
-> WHEN 5 THEN '木'
-> WHEN 6 THEN '金'
-> WHEN 7 THEN '土'
-> END as 曜日
-> FROM (SELECT 0 generate_series
-> FROM DUAL
-> WHERE (@num:=1-1)*0
-> UNION ALL
-> SELECT @num:=@num+1
-> FROM `information_schema`.COLUMNS
-> LIMIT 10) as tmp;
+------------+--------+
| 日付 | 曜日 |
+------------+--------+
| 2018-11-24 | 土 |
| 2018-11-25 | 日 |
| 2018-11-26 | 月 |
| 2018-11-27 | 火 |
| 2018-11-28 | 水 |
| 2018-11-29 | 木 |
| 2018-11-30 | 金 |
| 2018-12-01 | 土 |
| 2018-12-02 | 日 |
| 2018-12-03 | 月 |
+------------+--------+
10 rows in set (0.06 sec)今日から7日ごとの日付を10日分表示
mysql> SELECT date_format(date_add(CURDATE(), interval (tmp.generate_series - 1) * 7 day), '%Y-%m-%d') as 日付
-> FROM (SELECT 0 generate_series
-> FROM DUAL
-> WHERE (@num:=1-1)*0
-> UNION ALL
-> SELECT @num:=@num+1
-> FROM `information_schema`.COLUMNS
-> LIMIT 10) as tmp;
+------------+
| 日付 |
+------------+
| 2018-11-24 |
| 2018-12-01 |
| 2018-12-08 |
| 2018-12-15 |
| 2018-12-22 |
| 2018-12-29 |
| 2019-01-05 |
| 2019-01-12 |
| 2019-01-19 |
| 2019-01-26 |
+------------+
10 rows in set (0.07 sec)
