项目场景:
提示:这里简述项目相关背景:
例如:项目场景:mysql通过月份聚合操作,有的月份没有数据,这时候就无法聚合到这个月份,因此我们需要手动为其填充0
问题描述
SELECT
a.date,
sum( monthlyTotalPower ) monthlyTotalPower
FROM
(
SELECT
DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) `date`,
0 AS monthlyTotalPower
FROM
( SELECT @cdate := DATE_ADD( CURDATE( ), INTERVAL + 1 MONTH ) FROM kway_alien.campus_electric_info ) t1
WHERE
@cdate > '2022-02-01' UNION
SELECT
DATE_FORMAT( ei.create_time, '%Y-%m' ) `date`,
sum( ei.change_num ) monthlyTotalPower
FROM
kway_alien.campus_electric_info ei
WHERE
ei.type = 1
AND ei.user_id = 881
AND ei.state = 1
AND ei.enterprise_id = 705
AND ei.create_time > '2022-03-30'
GROUP BY
DATE_FORMAT( ei.create_time, '%Y-%m' )
) AS a
GROUP BY
a.date DESC
解决方案:
通过上文sql