你可以在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 ');

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.

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

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

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

如果你想过滤特定年份(例如2000年)的记录，那么优化WHERE子句，如下所示:

SELECT MONTH(date_column), COUNT(*)
FROM date_table
WHERE date_column >= '2000-01-01' AND date_column < '2001-01-01'
GROUP BY MONTH(date_column)
-- average 0.016 sec.

而不是:

WHERE YEAR(date_column) = 2000
-- average 0.132 sec.

结果是根据一个包含300k行和date列索引的表生成的。

至于GROUP BY子句，我根据上面提到的表测试了三个变体;以下是调查结果:

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY YEAR(date_column), MONTH(date_column)
-- codelogic
-- average 0.250 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY DATE_FORMAT(date_column, '%Y%m')
-- Andriy M
-- average 0.468 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY EXTRACT(YEAR_MONTH FROM date_column)
-- fu-chi
-- average 0.203 sec.

最后一个是赢家。

我更喜欢这样优化一年期组的选择:

SELECT COUNT(*)
  FROM stats
 WHERE record_date >= :year 
   AND record_date <  :year + INTERVAL 1 YEAR;

这样你就可以把年份绑定在一起。'2009'，带有命名参数，无需担心添加'-01-01'或单独传入'2010'。

另外，假设我们只是计算行数，id从不为NULL，我更喜欢COUNT(*)而不是COUNT(id)。