mysql 循环日历

基础概念

MySQL循环日历通常指的是在数据库中生成一个连续的日期序列，用于各种日期相关的查询和操作。这种日历可以是静态的（预先生成并存储在数据库中），也可以是动态的（在查询时实时生成）。

类型

静态日历表：在数据库中预先创建一个包含所有可能日期的表。
动态日历生成：在查询时使用SQL函数生成日期序列。

应用场景

时间序列分析：用于分析随时间变化的数据趋势。
报告生成：自动生成指定日期范围内的报告。
数据对齐：将不同时间粒度的数据对齐到统一的日期网格上。

示例代码（静态日历表）

-- 创建一个静态日历表
CREATE TABLE calendar (
    date DATE PRIMARY KEY,
    day_of_week INT,
    month_name VARCHAR(9),
    quarter INT,
    year INT
);

-- 插入数据（示例：插入未来一年的日期）
INSERT INTO calendar (date, day_of_week, month_name, quarter, year)
SELECT DATE_ADD(CURDATE(), INTERVAL n DAY) AS date,
       DAYOFWEEK(DATE_ADD(CURDATE(), INTERVAL n DAY)) AS day_of_week,
       MONTHNAME(DATE_ADD(CURDATE(), INTERVAL n DAY)) AS month_name,
       QUARTER(DATE_ADD(CURDATE(), INTERVAL n DAY)) AS quarter,
       YEAR(DATE_ADD(CURDATE(), INTERVAL n DAY)) AS year
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS n
    FROM (SELECT 0 AS N 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) a
    CROSS JOIN (SELECT 0 AS N 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) b
    CROSS JOIN (SELECT 0 AS N 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) c
) numbers
WHERE DATE_ADD(CURDATE(), INTERVAL n DAY) <= DATE_ADD(CURDATE(), INTERVAL 1 YEAR);

示例代码（动态日历生成）

-- 动态生成未来一个月的日期序列
SELECT DATE_ADD(CURDATE(), INTERVAL n DAY) AS date
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS n
    FROM (SELECT 0 AS N 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) a
    CROSS JOIN (SELECT 0 AS N 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) b
    CROSS JOIN (SELECT 0 AS N 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) c
    CROSS JOIN (SELECT 0 AS N 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) d
) numbers
WHERE DATE_ADD(CURDATE(), INTERVAL n DAY) <= DATE_ADD(CURDATE(), INTERVAL 1 MONTH);