MySQL-查询近一年或今年的汇总数据

描述

业务需求需要查询今年12个月的汇总数据。月份和数据都从sql语句执行生成

• 字母缩写
  • TB 操作表（TableName）
  • q_time 操作的时间字段（query_time）

查询年汇总数据

近一年

// sql
SELECT
	DATE_FORMAT( q_time, '%Y-%m' ) AS MONTH,
	SUM( water_inlet ) AS summarize_data
FROM
	TB
WHERE
	q_time BETWEEN DATE_SUB( NOW(), INTERVAL 12 MONTH ) 
	AND NOW() 
GROUP BY
MONTH 
ORDER BY
	MONTH;

今年

// sql
SELECT
	all_months.MONTH,
	COALESCE ( monthly_totals.summarize_data, 0 ) AS summarize_data
FROM
	(
	SELECT
		CONCAT(YEAR(CURDATE()), '-', LPAD( month_num, 2, '0' )) AS MONTH 
	FROM
		(
		SELECT
			1 AS month_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
		SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
		SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
		SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 
		) AS months 
	) AS all_months
	LEFT JOIN (
	SELECT
		DATE_FORMAT( q_time, '%Y-%m' ) AS MONTH,
		SUM( water_inlet ) AS summarize_data
	FROM
		TB
	WHERE
		q_time BETWEEN DATE_SUB( NOW(), INTERVAL 12 MONTH ) 
		AND NOW() 
	GROUP BY
	MONTH 
	) AS monthly_totals ON all_months.MONTH = monthly_totals.MONTH 
ORDER BY
	all_months.MONTH;