是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录，比如一年，一个月，或者一天，有一个TIMESTAMP字段，比如:
SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR
甚至:
SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH
每月进行统计。
谢谢!
当前回答
完整而简单的解决方案，具有类似的性能，但更短，更灵活的替代方案，目前活跃:
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')
其他回答
我更喜欢这样优化一年期组的选择:
SELECT COUNT(*)
FROM stats
WHERE record_date >= :year
AND record_date < :year + INTERVAL 1 YEAR;
这样你就可以把年份绑定在一起。'2009'，带有命名参数，无需担心添加'-01-01'或单独传入'2010'。
另外，假设我们只是计算行数，id从不为NULL，我更喜欢COUNT(*)而不是COUNT(id)。
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
完整而简单的解决方案，具有类似的性能，但更短，更灵活的替代方案，目前活跃:
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')
我试着使用上面的“WHERE”语句，我认为它是正确的，因为没有人纠正它，但我错了;经过一些搜索，我发现这是WHERE语句的正确公式，所以代码变成这样:
SELECT COUNT(id)
FROM stats
WHERE YEAR(record_date) = 2009
GROUP BY MONTH(record_date)
