56,679
社区成员
发帖
与我相关
我的任务
分享
MariaDB [demo]> create table t(id int primary key, d date);
Query OK, 0 rows affected (0.22 sec)
MariaDB [demo]> select * from t;
Empty set (0.00 sec)
那么select是
MariaDB [demo]> select t.*, m.*
-> from t right outer join
-> (select * from
-> (select @d := date_add(@d, interval 1 day) d
-> from
-> dummy,
-> (select
-> @b := str_to_date ('2016.4.1', '%Y.%m.%d'),
-> @d := date_add(@b, interval -1 day),
-> @e := date_add(@b, interval 1 month)
-> ) s
-> ) t_d
-> where d < @e) m
-> on (t.d = m.d)
-> ;
+------+------+------------+
| id | d | d |
+------+------+------------+
| NULL | NULL | 2016-04-01 |
| NULL | NULL | 2016-04-02 |
| NULL | NULL | 2016-04-03 |
| NULL | NULL | 2016-04-04 |
| NULL | NULL | 2016-04-05 |
| NULL | NULL | 2016-04-06 |
| NULL | NULL | 2016-04-07 |
| NULL | NULL | 2016-04-08 |
| NULL | NULL | 2016-04-09 |
| NULL | NULL | 2016-04-10 |
| NULL | NULL | 2016-04-11 |
| NULL | NULL | 2016-04-12 |
| NULL | NULL | 2016-04-13 |
| NULL | NULL | 2016-04-14 |
| NULL | NULL | 2016-04-15 |
| NULL | NULL | 2016-04-16 |
| NULL | NULL | 2016-04-17 |
| NULL | NULL | 2016-04-18 |
| NULL | NULL | 2016-04-19 |
| NULL | NULL | 2016-04-20 |
| NULL | NULL | 2016-04-21 |
| NULL | NULL | 2016-04-22 |
| NULL | NULL | 2016-04-23 |
| NULL | NULL | 2016-04-24 |
| NULL | NULL | 2016-04-25 |
| NULL | NULL | 2016-04-26 |
| NULL | NULL | 2016-04-27 |
| NULL | NULL | 2016-04-28 |
| NULL | NULL | 2016-04-29 |
| NULL | NULL | 2016-04-30 |
+------+------+------------+
30 rows in set (0.00 sec)
MariaDB [demo]> create table dummy(id int primary key);
Query OK, 0 rows affected (0.25 sec)
MariaDB [demo]> insert into dummy values
-> (1),
-> (2),
-> (3),
-> (4),
-> (5),
-> (6),
-> (7),
-> (8),
-> (9),
-> (10),
-> (11),
-> (12),
-> (13),
-> (14),
-> (15),
-> (16),
-> (17),
-> (18),
-> (19),
-> (20),
-> (21),
-> (22),
-> (23),
-> (24),
-> (25),
-> (26),
-> (27),
-> (28),
-> (29),
-> (30),
-> (31);
Query OK, 31 rows affected (0.05 sec)
Records: 31 Duplicates: 0 Warnings: 0
MariaDB [demo]> select * from dummy;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
+----+
31 rows in set (0.00 sec)
第二步,执行select
因为一个月的天数脱离具体的年是没有意义的,所以我用字符串给出了具体日期,你可以在你的程序中变通处理。
MariaDB [demo]> select * from
-> (select @d := date_add(@d, interval 1 day) d
-> from
-> dummy,
-> (select
-> @b := str_to_date ('2016.4.1', '%Y.%m.%d'),
-> @d := date_add(@b, interval -1 day),
-> @e := date_add(@b, interval 1 month)
-> ) s
-> ) t_d
-> where d < @e;
+------------+
| d |
+------------+
| 2016-04-01 |
| 2016-04-02 |
| 2016-04-03 |
| 2016-04-04 |
| 2016-04-05 |
| 2016-04-06 |
| 2016-04-07 |
| 2016-04-08 |
| 2016-04-09 |
| 2016-04-10 |
| 2016-04-11 |
| 2016-04-12 |
| 2016-04-13 |
| 2016-04-14 |
| 2016-04-15 |
| 2016-04-16 |
| 2016-04-17 |
| 2016-04-18 |
| 2016-04-19 |
| 2016-04-20 |
| 2016-04-21 |
| 2016-04-22 |
| 2016-04-23 |
| 2016-04-24 |
| 2016-04-25 |
| 2016-04-26 |
| 2016-04-27 |
| 2016-04-28 |
| 2016-04-29 |
| 2016-04-30 |
+------------+
30 rows in set (0.00 sec)