MySQL 实现柱状(折线)图统计

MySQL 查询指定间隔的连续日期记录

SELECT
	DATE_FORMAT( DATE_ADD( SUBDATE( NOW(), INTERVAL 11 MONTH ), INTERVAL ( help_topic_id ) MONTH ), '%Y-%m' ) `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id <= TIMESTAMPDIFF( MONTH, SUBDATE( NOW(), INTERVAL 11 MONTH ), NOW())

SELECT
	DATE_FORMAT( DATE_ADD( SUBDATE( NOW(), INTERVAL 11 YEAR ), INTERVAL ( help_topic_id ) YEAR ), '%Y' ) `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id <= TIMESTAMPDIFF( YEAR, SUBDATE( NOW(), INTERVAL 11 YEAR ), NOW())

与查询记录关联

SELECT
	a.`date` AS `date`,
	IFNULL(b.total, 0) AS `value`
FROM
	<calendar> AS a
LEFT JOIN
	<costom> AS b
ON 
	a.`date` = b.`date`