526互联

mysql统计 不含的月份补0

发布时间 2023-10-27 09:20:21作者: ジ绯色月下ぎ

构造当前日期之前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`