．．．． 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 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的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

试一试

按年(record_date)，月(record_date)分组

GROUP BY YEAR(record_date), MONTH(record_date)

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

我希望每天都能得到类似的数据，经过一些试验，这是我在这个场景中能找到的最快的数据

SELECT COUNT(id)
FROM stats
GROUP BY record_date DIV 1000000;

如果你想每个月有一次，添加额外的零(00) 我不建议从“使代码可读”的角度考虑，它也可能在不同的版本中中断。但在我们的例子中，与我测试的其他更清晰的查询相比，这只花了不到一半的时间。

这是一个MySQL的答案(因为MySQL被标记在问题中)，并在手册https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html中有详细的说明