sql日期辅助表,日期sql(可直接运行)

mysql 生成日期辅助表,不需要创建表  

此文转自:https://www.cnblogs.com/lancelee98/p/10006710.html ,直接可运行,方便省事,如不符合可根据原文改写

 

select * from (SELECT ADDDATE((DATE_FORMAT( "2020-01-01", '%Y-%m-%d' )) , (n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000) ) AS datenum FROM(SELECT 0 AS i UNION ALL SELECT 1 AS i 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 n1 CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 AS i 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 i UNION ALL SELECT 1 AS i 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 i UNION ALL SELECT 1 AS i 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 CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 AS i 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 n10000) as datelist where datenum like '2020-05%'
 

 

注意:多次尝试,发现复制出去可能会多出空格导致sql运行错误,所以所有空格都是1个,有多出的请删除后尝试运行