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