mysql数据趋势图日期补全关联表设计

趋势图时间表设计：
预先插入一部分日期到关联表，根据业务情况尽可能推后10年以上。

CREATE TABLE t_calendar (
    date_time DATE PRIMARY KEY
);

-- 填充日期数据
INSERT INTO t_calendar (date_time)
SELECT DATE('2023-01-01') + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
            UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
            UNION ALL SELECT 8 UNION ALL SELECT 9) AS c;
            

效果：