mysql统计 不含的月份补0

构造当前日期之前10天的日期表  可以把DAY换成 MONTH 构造需要的月份   不需要固定天数的 可以去掉limit

SELECT
    @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date 
FROM
    ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM APPLY LIMIT 10 ) a

 

正常写统计sql

select DATE_FORMAT( o.deadline, '%Y/%m' ) as 'date', SUM(1) addCount from u_model m,u_other o   
            where m.up_code = o.up_code  
            and o.deadline BETWEEN #{now} and #{future}  
            group by `date`

 

最后以日期表为主表 左连接  业务sql的结果聚合表

select table1.`date` as 'date',cast(ifnull(table2.`addCount`, 0) as char) as 'addCount' from  
             (SELECT DATE_FORMAT( @cdate := DATE_ADD(@cdate,INTERVAL - 1 month), '%Y/%m' ) `date`,0 `addCount`  
             FROM (SELECT @cdate :=DATE_ADD('2024-03-01', INTERVAL 1 month) FROM u_other) t1   
             WHERE @cdate > #{now} ) table1 left join  
             (select DATE_FORMAT( o.deadline, '%Y/%m' ) as 'date', SUM(1) addCount from u_model m,u_other o   
            where m.up_code = o.up_code  
            and o.deadline BETWEEN #{now} and #{future}  
            group by `date`) table2  on  table1.`date` = table2.`date` order by table1.`date`  

 

或者直接构造对应的月份

-- 查询当前日期往后半年  curdate() 可以换成'2024-07-16'这种日期类型

SELECT
        DATE_FORMAT(DATE_ADD(CURDATE() ,
        INTERVAL a.a MONTH),
        '%Y/%m') AS `date` ,
        0 `addCount` 
    FROM
        (SELECT
            0 a 
        UNION
        ALL SELECT
            1 
        UNION
        ALL SELECT
            2 
        UNION
        ALL SELECT
            3 
        UNION
        ALL SELECT
            4 
        UNION
        ALL SELECT
            5
    ) AS a 
ORDER BY
    `date`