MySQL 如何生成日期表

 1 -- CREATE TABLE num (i int);
 2 -- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 3 -- CREATE TABLE  if not exists calendar(datelist date); 
 4 INSERT INTO calendar(datelist) SELECT
 5     adddate(
 6         (   -- 这里的起始日期，你可以换成当前日期
 7             DATE_FORMAT("2016-1-1", ‘%Y-%m-%d‘) 
 8         ),
 9         numlist.id
10     ) AS `date`
11 FROM
12     (
13         SELECT
14             n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
15         FROM
16             num n1
17         CROSS JOIN num AS n10
18         CROSS JOIN num AS n100
19         CROSS JOIN num AS n1000
20         CROSS JOIN num AS n10000
21     ) AS numlist;

结果所示：

5、最后再添加主键即可

 1 -- CREATE TABLE num (i int);
 2 -- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 3 -- CREATE TABLE  if not exists calendar(datelist date); 
 4 -- INSERT INTO calendar(datelist) SELECT
 5 --     adddate(
 6 --         (   -- 这里的起始日期，你可以换成当前日期
 7 --             DATE_FORMAT("2016-1-1", ‘%Y-%m-%d‘) 
 8 --         ),
 9 --         numlist.id
10 --     ) AS `date`
11 -- FROM
12 --     (
13 --         SELECT
14 --             n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
15 --         FROM
16 --             num n1
17 --         CROSS JOIN num AS n10
18 --         CROSS JOIN num AS n100
19 --         CROSS JOIN num AS n1000
20 --         CROSS JOIN num AS n10000
21 --     ) AS numlist;
22 ALTER TABLE `calendar`
23 ADD COLUMN `id`  int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键‘ FIRST ,
24 ADD PRIMARY KEY (`id`);

最终结果如图所示：

然后再去统计数据的时候关联该日期表就行了

MySQL 如何生成日期表

标签：结果   步骤   技术   ddd   mic   创建   column   tool   copy