SELECT
IFNULL(b.num,0) AS SUM,a.date
FROM
(
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 7 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 8 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 9 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 10 MONTH), '%Y-%m') AS DATE UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH), '%Y-%m') AS DATE
)a
LEFT JOIN
(
SELECT SUM(需要统计的字段)AS num, DATE_FORMAT(创建时间, '%Y-%m') AS create_time FROM 表名 GROUP BY DATE_FORMAT(创建时间, '%Y-%m')
) b
ON a.date = b.create_time
ORDER BY a.date;
运行效果图
如果发现博文有问题,欢迎各位老鸟多多指教