SELECT date as date, IFNULL(tab.num, 0) as num FROM (
# 生成月份表 @符号定义一个变量,而不是识别为列名
SELECT
DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as date
FROM
(
SELECT @cdate := date_add(CURDATE(), INTERVAL 1 MONTH )
FROM fault LIMIT 12)d
ORDER BY date
)date_c LEFT JOIN (
# 查询数据
SELECT COUNT(*) as num, DATE_FORMAT(fault_time,'%Y-%m') as t
FROM fault WHERE 1=1 GROUP BY DATE_FORMAT(fault_time,'%Y-%m')
)tab ON t=date
解释:
注意:FROM fault LIMIT 12中的表fault,数据必须大于12,否则会出错,可以换成数据量一定大于12的表
fault 为要查询的表
fault_time 为筛选的时间列