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;

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‘);