mysql 通过变量补充查询返回

获取‘t_test’表近6个月数据（排除当前月），没有数据的月份用0补充

SELECT
	date_format( months.`month`, '%Y-%m' ) AS `month`,
	ifnull( datas.dataCount, 0 ) AS dataCount 
FROM
	(
SELECT
	@d := DATE_ADD( @d, INTERVAL 1 MONTH ) `month` 
FROM
	( SELECT @d := DATE_ADD( now( ), INTERVAL - 7 MONTH ) FROM t_test LIMIT 6 ) m 
	) months
	LEFT JOIN (
	SELECT
	date_format( create_time, '%Y-%m' ) AS `month`,
	count( * ) AS dataCount 
FROM
	t_test
WHERE
	date_format( create_time, '%Y-%m' ) >= date_format( DATE_ADD( now( ), INTERVAL - 6 MONTH ), '%Y-%m' ) 
	AND date_format( create_time, '%Y-%m' ) < date_format( now( ), '%Y-%m' ) 
GROUP BY
	date_format( create_time, '%Y-%m' )
	) datas on datas.`month` = date_format( months.`month`, '%Y-%m' )
ORDER BY
	`month` ASC

获取‘t_test’表24小时统计，没有数据的补充0

SELECT
	A.`hour`,
	ifnull( datas.trappCount, 0 ) AS trappCount 
FROM
	(
SELECT
	DATE_FORMAT(
	DATE_SUB( DATE_FORMAT( NOW( ), '%Y-%m-%d' ), INTERVAL ( - ( @i := @i + 1 ) ) HOUR ),
	'%H' 
	) AS 'hour' 
FROM
	(
SELECT
	a 
FROM
	( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
	JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 
	) AS b,
	( SELECT @i :=- 1 ) AS i 
	) A
	LEFT JOIN ( SELECT DATE_FORMAT( create_time, '%H' ) `hour`, count( * ) trappCount FROM t_test GROUP BY DATE_FORMAT( create_time, '%H' ) ) datas ON A.`hour` = datas.`hour`