描述
业务需求需要查询今年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;