mysql按月统计最近一年,半年数量,本月每一天

需求如下,要统计最近半年,一年的数量,数据为0也需要

直接查表统计

有一些月份没有数据,无法展示

查询最近一年的月份

SELECT
		@s := @s + 1 AS `index`,
		DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL @s MONTH ), '%Y-%m' ) AS `mon` 
	FROM
		mysql.help_topic,
		( SELECT @s := - 1 ) temp 
	WHERE
		@s < 11 
	ORDER BY
	mon DESC 

运行结果

这里改成5就是查半年

完整SQL示例

SELECT
	t.mon,
	( SELECT count( 1 ) FROM work_order wo WHERE LEFT ( wo.reserve_time, 7 ) = t.mon ) AS workNum 
FROM
	(
	SELECT
		@s := @s + 1 AS `index`,
		DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL @s MONTH ), '%Y-%m' ) AS `mon` 
	FROM
		mysql.help_topic,
		( SELECT @s := - 1 ) temp 
	WHERE
		@s < 11 
	ORDER BY
	mon DESC 
	) AS t

自动补0

另外附上查询最近七天的sql

SELECT
	@s := @s + 1 AS `index`,
	DATE(
	DATE_SUB( CURRENT_DATE, INTERVAL @s DAY )) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s := - 1 ) temp 
WHERE
	@s < 6 
ORDER BY
	`date` desc

查询本月每天的日期

方案一

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