需求:统计今年的每个月的设备故障数,如果那个月没有需要补0。
比如,当前是7月,那就统计1月到7月的数据,如果当前是1月,那就统计1月的,如果是11月,那就统计1月到11月的。
直接对表分组的话,如果5月没有数据,是没有5月这个结果的,所以需要创建虚拟时间表,然后对数据空的月份进行补0。
base_city 是随便一个表就行,只要保证里面的数据一定是大于12条的,这里用的是省市区基地址表
SELECT
monthTime as month,
MAX(alarmDeviceNum) alarmDeviceNum
FROM (
SELECT
monthTime,
alarmDeviceNum
FROM (
SELECT
DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 MONTH ) , '%Y-%m' ) monthTime,
0 AS alarmDeviceNum
FROM(
SELECT
@cdate := DATE_ADD( CURDATE(), INTERVAL 1 MONTH )
FROM base_city LIMIT 12
) t1
) t2
UNION ALL
SELECT
DATE_FORMAT( a.biz_time, '%Y-%m' ) monthTime,
COUNT(DISTINCT a.product_key) alarmDeviceNum
FROM
alarm_log a
WHERE 1=1
AND YEAR( a.biz_time)=YEAR( CURDATE())
GROUP BY DATE_FORMAT( a.biz_time, '%Y-%m' )
) f
GROUP BY monthTime