因工作需要,按月生成报表,如果仅仅是按照月筛选(WHERE)出需要的数据再按照每天的日期分组(GROUP BY),那么某日期没有数据,就会导致查询的数据的日期缺失,由此想到将查到的数据表与一张能查询到所有当月下的所有日期的表进行连接(LEFT JOIN)空的月份用(IFNULL)补0。参考了网上大佬的代码。在此记录。
https://www.cnblogs.com/jpfss/p/11131988.html
下面附上代码:
方案一:
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
方案二:
SELECT
date_add(
CONCAT(YEAR(Date(curdate())),'-0',MONTH(Date(curdate())),'-','01'),
INTERVAL ( cast( help_topic_id AS signed INTEGER ) ) DAY
) DAY
FROM
mysql.help_topic
WHERE
help_topic_id < DAY ( last_day( curdate( ) ) )
ORDER BY
help_topic_id