mysql将某一个月所有天数构造出来

写项目时会遇到统计某个月每一天数据的场景

mysql可以将某个月的所有日期构造出来

DAY()函数：返回给定日期的月份的日期部分

LAST_DAY()函数：返回某个月最后一天的日期

STR_TO_DATE()函数：将字符串格式转换成日期格式

ADDDATE()函数：将指定的日期值添加到现有日期上，并返回结果

SELECT ADDDATE(month.firstDay, days.day - 1) as result
FROM
(SELECT 1 AS day 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 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) days,
(SELECT '2023-09-01' as firstDay, DAY(LAST_DAY(str_to_date('2023-09-01','%Y-%m-%d'))) AS dayNum) month
WHERE days.day <= month.dayNum
————————————————