这里还有一种方法。这使用[MySQL的LAST_DAY()函数][1]将每个时间戳映射到它的月份。如果在record_date上有索引，它还能够通过有效的范围扫描按年进行过滤。

  SELECT LAST_DAY(record_date) month_ending, COUNT(*) record_count
    FROM stats
   WHERE record_date >= '2000-01-01'
     AND record_date <  '2000-01-01' + INTERVAL 1 YEAR
   GROUP BY LAST_DAY(record_date) 

如果希望按天计算结果，请使用DATE(record_date)。

如果您希望按日历季度获得结果，请使用YEAR(record_date)， quarter (record_date)。

这是一个记录。https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/ [1]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html # function_last-day

．．．． group by to_char(date， 'YYYY')——> 1989

．．．． group by to_char(date，'MM')——>05

．．．． 3 .用to_char(date，'DD')——>

．．．． group by to_char(date，'MON')——>

．．．． 9 . group by to_char(date，'YY')——>

GROUP BY YEAR(record_date), MONTH(record_date)

查看MySQL中的日期和时间函数。

我试着使用上面的“WHERE”语句，我认为它是正确的，因为没有人纠正它，但我错了;经过一些搜索，我发现这是WHERE语句的正确公式，所以代码变成这样:

SELECT COUNT(id)  
FROM stats  
WHERE YEAR(record_date) = 2009  
GROUP BY MONTH(record_date)

GROUP BY DATE_FORMAT(record_date, '%Y%m')

Note (primarily, to potential downvoters). Presently, this may not be as efficient as other suggestions. Still, I leave it as an alternative, and a one, too, that can serve in seeing how faster other solutions are. (For you can't really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQL's engine with regard to optimisation so as to make this solution, at some (perhaps, not so distant) point in future, to become quite comparable in efficiency with most others.

如果你想在MySQL中按日期分组，那么使用下面的代码:

 SELECT COUNT(id)
 FROM stats
 GROUP BY DAYOFMONTH(record_date)

希望这为那些要找到这个帖子的人节省了一些时间。