按月统计数据——mysql实现

业务场景
对某类业务数据，按月统计数量，返回当年各个月份的任务数量。
思路
按照创建时间，格式化为yyyy-mm的month字段，然后根据month进行分组查询，统计count作为数量

SELECT DATE_FORMAT(create_time, '%Y-%m') AS month,
               COUNT(*)                          as allNum,
               COUNT(IF(status = 4 and DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(end_time, '%Y-%m'), TRUE,
                        NULL))                   as completeNum
        FROM engineering_task
        WHERE task_type = 5
          AND create_time between '2023-01-01' AND '2023-09-19'
          AND is_del = 0
        GROUP BY month
1
2
3
4
5
6
7
8
9

结果示例