mysql 查询本月所有天数统计对应数据

用到的函数：

获取本月第一天：

select date_add(curdate(),interval -day(curdate())+1 day)

获取本月最后一天：

select last_day(curdate())

分为三个部分：

1.根据时间分组统计数据 

	SELECT
		count(*) AS sum,
		DATE_FORMAT( createOn, '%Y-%m-%d' ) AS 'date' 
	FROM
		work_schdule_item_info a 
	WHERE
		DATE_FORMAT( createOn, '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' ) 
	GROUP BY
		date

2.获取本月所有日期，默认统计数0

这里参考：

 获取本月的默认统计数据：

SELECT  0 as `sum`,@cdate := date_add(@cdate,interval - 1 day) `date`
from (SELECT @cdate :=date_add(last_day(curdate()),interval + 1 day) from work_schdule_item_info) t1 
where @cdate > (date_add(curdate(),interval -day(curdate())+1 day)) 

 3.合并结果集：

SELECT
	`date`,
	max( `sum` ) AS `sum` 
FROM
	(
	SELECT
		count(*) AS sum,
		DATE_FORMAT( createOn, '%Y-%m-%d' ) AS 'date' 
	FROM
		work_schdule_item_info a 
	WHERE
		DATE_FORMAT( createOn, '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' ) 
	GROUP BY
		date UNION ALL
	SELECT
		0 AS `sum`,
		@cdate := date_add( @cdate, INTERVAL - 1 DAY ) `date` 
	FROM
		( SELECT @cdate := date_add( last_day( curdate()), INTERVAL + 1 DAY ) FROM work_schdule_item_info ) t1 
	WHERE
		@cdate > (
		date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY )) 
	) _tmpAllTable 
GROUP BY
	`date`