MySQL根据某个表的日期范围字段转换生成每天一条记录

生成单独的日期表(从2015-01-01到2042-05-18每天一条记录)：

SELECT ADDDATE(“2015-01-01”, INTERVAL numT.num DAY) AS _date FROM

(SELECT

n1.v + n10.v * 10 + n100.v * 100 + n1000.v * 1000 AS num

FROM

(SELECT 0 AS v 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) n1

CROSS JOIN (SELECT 0 AS v 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 n10

CROSS JOIN (SELECT 0 AS v 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 n100

CROSS JOIN (SELECT 0 AS v 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 n1000

) AS numT;

说明：

• numT 表为自动生成的临时表，只有一个num字段，共10000条记录，记录值为0~9999。
• n1, n10, n100, n1000 这4个表是一样的，只有一个字段v，共10条记录，记录值为0~9。
• 因为这里只生成了最大10000条num记录，所以只支持最大10000天，要更大的话，可以再join增加n10000类似表，就可以大10倍了。

根据其他表的开始结束日期，生成每日一条记录：

SELECT ADDDATE(t.bid_date, INTERVAL numT.num DAY) AS _date, t.* FROM

t_cpe_event_config AS t,

(SELECT

n1.v + n10.v * 10 + n100.v * 100 + n1000.v * 1000 AS num

FROM

(SELECT 0 AS v 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) n1

CROSS JOIN (SELECT 0 AS v 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 n10

CROSS JOIN (SELECT 0 AS v 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 n100

CROSS JOIN (SELECT 0 AS v 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 n1000

) AS numT

WHERE numT.num BETWEEN 0 AND DATEDIFF(t.bid_date_end, t.bid_date)

AND t._id=908;

说明：

• t_cpe_event_config 表有三个以上字段： id，  bid_date(开始日期)，  bide_date_end(结束日期)，…。
• numT 表为自动生成的临时表，只有一个num字段，共10000条记录，记录值为0~9999。
• n1, n10, n100, n1000 这4个表是一样的，只有一个字段v，共10条记录，记录值为0~9。
• 因为这里只生成了最大10000条num记录，所以只支持最大10000天，要更大的话，可以再join增加n10000类似表，就可以大10倍了。