MySQL 如何生成日期表
在开发过程中,经常会遇到统计问题,通常交易信息都不是连续的,此时,统计出来的数据都是不连续的,所以提前生成一个时期表,当没有交易数据的时候填充0,就可以了,下面是生成日期表的步骤
1、创建一个num表,用来存储数字0~9
1 CREATE TABLE num (i int);
结果如下:
2、在num表中生成0~9
1 -- CREATE TABLE num (i int); 2 INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
结果如下:
3、生成一个存储日期的表,datalist是字段名
-- CREATE TABLE num (i int); -- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); CREATE TABLE if not exists calendar(datelist date);
结果如下:
4、生成并插入日期数据
-- CREATE TABLE num (i int); -- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); -- CREATE TABLE if not exists calendar(datelist date); INSERT INTO calendar(datelist) SELECT adddate( ( -- 这里的起始日期,你可以换成当前日期 DATE_FORMAT("2016-1-1", ‘%Y-%m-%d‘) ), numlist.id ) AS `date` FROM ( SELECT n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100 CROSS JOIN num AS n1000 CROSS JOIN num AS n10000 ) AS numlist;
结果所示:
5、最后再添加主键即可
-- CREATE TABLE num (i int); -- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); -- CREATE TABLE if not exists calendar(datelist date); -- INSERT INTO calendar(datelist) SELECT -- adddate( -- ( -- 这里的起始日期,你可以换成当前日期 -- DATE_FORMAT("2016-1-1", ‘%Y-%m-%d‘) -- ), -- numlist.id -- ) AS `date` -- FROM -- ( -- SELECT -- n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id -- FROM -- num n1 -- CROSS JOIN num AS n10 -- CROSS JOIN num AS n100 -- CROSS JOIN num AS n1000 -- CROSS JOIN num AS n10000 -- ) AS numlist; ALTER TABLE `calendar` ADD COLUMN `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键‘ FIRST , ADD PRIMARY KEY (`id`);
最终结果如图所示:
然后再去统计数据的时候关联该日期表就行了
相关推荐
mycosmos 2020-02-01
哈嘿Blog 2020-10-26
明月清风精进不止 2020-07-05
PythonMaker 2020-07-05
xirongxudlut 2020-06-28
kkpiece 2020-06-16
qscool 2020-06-12
CloudXli 2020-06-11
vs00ASPNET 2020-06-09
Dimples 2020-06-08
kuoying 2020-06-07
JJandYY 2020-05-31
Wyt00 2020-05-30
liuyh 2020-04-03
CloudXli 2020-05-11
世樹 2020-05-11
bizercsdn 2020-05-10