本文介绍了如何使用MySQL中的DAY(),LAST_DAY(),STR_TO_DATE()和ADDDATE()函数来生成2023年9月每一天的日期，通过UNIONALL操作和ADDDATE()计算出该月每一天的日期范围。
摘要由CSDN通过智能技术生成
SELECT ADDDATE(month.firstDay, days.day - 1) as result
(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
SELECT DATE_ADD('2023-02-01', INTERVAL n DAY)
FROM (
SELECT a.N + b.N * 10 + c.N * 100 AS n
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL...
因工作需要，按月生成报表，如果仅仅是按照月筛选（WHERE）出需要的数据再按照每天的
日期
分组（GROUP BY），那么某
日期
没有数据，就会导致查询的数据的
日期
缺失，由此想到将查到的数据表与一张能查询到所有当月下的所有
日期
的表进行连接（LEFT JOIN）空的
月份
用(IFNULL)补0。参考了网上大佬的代码。在此记录。
https://www.cnblogs.com/jpfss/p/1113198...
SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY), INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY ) DAY
from
mysql
.help_topic
where help_topic_id < day(last_day(curdate()))
order by help_topic_id
您可以使用以下
MySQL
查询来生成近 30 天的所有
日期
：
SELECT DATE_ADD(CURDATE(), INTERVAL -30 + NUM DAY) AS date
FROM (
SELECT @i := @i + 1 AS NUM
FROM (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SEL...
select date_add(curdate(), interval(cast(help_topic_id as signed integer) - 30) day) day
from
mysql
.help_topic
where help_topic_id < day(last_day(curdate()))
order by help_topic_id
CSDN-Ada助手:
