mysql 循环日历

-- 创建一个静态日历表
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);