Msyql 查询当前月份前12个月，每月统计

 

Mysql 查询当前月份前12个月数据统计。如果求和可使用SUM函数

 

SELECT a.month, IFNULL(b.count,0) AS count FROM(
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
FROM table_equip )a
LEFT JOIN (SELECT COUNT(equip_id) as count,create_date as date from table_equip
GROUP BY(DATE_FORMAT(create_date, '%Y-%m'))) b on a.month = DATE_FORMAT(b.date, '%Y-%m') ORDER BY a.month asc

结果如下：

提醒： 使用时需更换下表名，以及创建时间create_date字段 和 equip_id （表主键）