My Sql 统计12个月的数据，无数据填充0

统计某年12个月的数据

语句：

 

select
a.date,ifnull(b.num,0) count
from
(
SELECT CONCAT('2020年','1月') AS date UNION
SELECT CONCAT('2020年','2月') AS date UNION
SELECT CONCAT('2020年','3月') AS date UNION
SELECT CONCAT('2020年','4月') AS date UNION
SELECT CONCAT('2020年','5月') AS date UNION
SELECT CONCAT('2020年','6月') AS date UNION
SELECT CONCAT('2020年','7月') AS date UNION
SELECT CONCAT('2020年','8月') AS date UNION
SELECT CONCAT('2020年','9月') AS date UNION
SELECT CONCAT('2020年','10月') AS date UNION
SELECT CONCAT('2020年','11月') AS date UNION
SELECT CONCAT('2020年','12月') AS date
)a
left join
(select count(*) num,DATE_FORMAT(HappenTime,'%Y年%c月') months from orders group by DATE_FORMAT(CreationTime,'%Y-%m')) b
on a.date = b.months

 

注：上面的"2020年"可根据实际情况以参数的形式传入。

结果：

 

 统计当前往前推12个月的数据统计

语句：

CURDATE() - INTERVAL 1 MONTH表示当前时间往前推一个月

select a.`year_month` months ,ifnull(b.num,0) count 
from
    (SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `year_month` 
     UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `year_month`) a
     left join 
        (select count(*) num, DATE_FORMAT(CreationTime,'%Y-%m') months  from records where  IsDeleted=0 group by DATE_FORMAT(CreationTime,'%Y-%m')) b
        on a.`year_month` =b.months order by months

结果：

当前时间为2023-5