MySQL按月分组查询日期显示异常，需统一显示每月1号

问题原因 #

当前查询直接选取created_on作为时间列，而GROUP BY MONTH(created_on)时，MySQL会返回该月份分组内任意一条记录的created_on值，因此出现了3月显示22号这类随机日期的情况。另外原查询仅按月份分组排序，会导致不同年份的同月份数据被错误合并，逻辑存在漏洞。

修改后的查询语句 #

SELECT
  DATE_FORMAT(created_on, '%Y-%m-01') AS 'time',
  MONTHNAME(created_on) AS 'month_name',
  COUNT(DISTINCT id_number) AS 'inc_number'
FROM test_reports
WHERE
  -- 建议补充具体时间范围筛选，例如 created_on BETWEEN '2021-01-01' AND '2022-12-31'
  created_on IS NOT NULL
GROUP BY YEAR(created_on), MONTH(created_on)
ORDER BY YEAR(created_on), MONTH(created_on)

关键修改点说明 #

• 生成当月第一天：用DATE_FORMAT(created_on, '%Y-%m-01')将任意日期格式化为当月1号的标准格式，也可以用以下等价写法：
  • DATE_ADD(created_on, INTERVAL -DAY(created_on)+1 DAY)
  • STR_TO_DATE(CONCAT(YEAR(created_on), '-', MONTH(created_on), '-01'), '%Y-%m-%d')
• 修正分组逻辑：将GROUP BY MONTH(created_on)改为GROUP BY YEAR(created_on), MONTH(created_on)，避免不同年份的同月份数据被合并。
• 修正排序逻辑：排序时同时按年份和月份排序，保证结果按时间顺序正确排列。

修改后的结果示例 #

time       month_name  inc_number
2021-12-01 December    8521
2022-01-01 January     39393
2022-02-01 February    90203
2022-03-01 March       82923
2022-04-01 April       23333
2022-05-01 May         33322
2022-06-01 June        33244
2022-07-01 July        90283
2022-08-01 August      8428
2022-09-01 September   10097
2022-10-01 October     6421

内容的提问来源于stack exchange，提问作者user9300309