mysql按日期分组统计数据

一，最近十二个月：没有数据的月份不展示

SELECT COUNT(*) AS visitCount, DATE_FORMAT(create_time, '%m') AS month FROM auditlog
        WHERE operate_type = 10 AND DATE_FORMAT(create_time,'%Y-%m')>
        DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m')
        GROUP BY month;

 二，最近十二个月，没有数据的补0

SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT(CURDATE(), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')
        UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS month ,COUNT(id) AS visitCount FROM auditlog WHERE operate_type = 10 AND DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') = DATE_FORMAT(create_time,'%Y-%m')

 

 

 三，所有数据按月统计，没有数据的月份不展示

select date_format(create_time, '%m') month, count(*) visitCount
from auditlog WHERE operate_type = 10
group by date_format(create_time, '%Y-%m');