GROUP BY YEAR(record_date), MONTH(record_date)

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

．．．． 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')——>

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

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中有详细的说明

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.

如果你想获得一个每月统计数据，每个月的行数按最近一个月排序，那么试试这个:

SELECT count(id),
      YEAR(record_date),
      MONTH(record_date) 
FROM `table` 
GROUP BY YEAR(record_date),
        MONTH(record_date) 
ORDER BY YEAR(record_date) DESC,
        MONTH(record_date) DESC

你可以在GROUP BY中简单的使用Mysql DATE_FORMAT()函数。在某些情况下，您可能希望添加一个额外的列以增加清晰度，例如记录跨越数年，而同一个月出现在不同的年份。这里有很多选项，你可以自定义。开始前请先读一下。希望对你有帮助。下面是示例查询，以帮助您理解

SELECT
    COUNT(id),
    DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY,
    DATE_FORMAT(record_date, '%Y-%m') AS MONTH,
    DATE_FORMAT(record_date, '%Y') AS YEAR

FROM
    stats
WHERE
    YEAR = 2009
GROUP BY
    DATE_FORMAT(record_date, '%Y-%m-%d ');