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

最新推荐文章于 2025-04-21 11:27:12 发布

转载最新推荐文章于 2025-04-21 11:27:12 发布 · 1.3k 阅读

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了一种使用SQL在数据库中生成连续日期和时间序列的方法。通过创建一个包含0到9数字的表，并利用这些数字进行交叉连接，可以生成大规模的日期和时间戳数据。这种方法适用于需要大量测试数据或初始化日历表的场景。

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