mysql生成日期的辅助表，为了解决mysql按日期分组查询统计的时候，没有数据补0

CREATE TABLE num ( i INT );
-- 创建一个表用来储存0-9的数字
INSERT INTO num ( i )
VALUES
    ( 0 ),
    ( 1 ),
    ( 2 ),
    ( 3 ),
    ( 4 ),
    ( 5 ),
    ( 6 ),
    ( 7 ),
    ( 8 ),
    ( 9 );
    
-- --------------------------------------------------------------

CREATE TABLE
IF
    NOT EXISTS calendar ( datelist date, datehour datetime );
    
-- --------------------------------------------------------------
INSERT INTO calendar ( datelist ) SELECT -- 生成按天的数据
adddate( ( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT( "2018-11-10", '%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;
    
-- --------------------------------------------------------------
INSERT INTO calendar ( datehour ) SELECT -- 生成按小时的数据
DATE_ADD( ( DATE_FORMAT( "2018-11-10", '%Y-%m-%d' ) ), INTERVAL numlist.id HOUR ) 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;

 

转载地址：https://www.cnblogs.com/lancelee98/p/10006710.html?tdsourcetag=s_pctim_aiomsg