MySQL MySQL查询GROUP BY日/月/年
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/508791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Query GROUP BY day / month / year
提问by Fernando Barrocal
Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMPfield, like:
是否可以进行一个简单的查询来计算我在确定的时间段内（例如年、月或日）有多少记录，有一个TIMESTAMP字段，例如：
SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR
Or even:
甚至：
SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH
To have a monthly statistic.
要有月度统计。
Thanks!
谢谢！
回答by codelogic
GROUP BY YEAR(record_date), MONTH(record_date)
Check out the date and time functionsin MySQL.
查看MySQL 中的日期和时间函数。
回答by Andriy M
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 的引擎进行更改，以便在某些情况下（也许不是这样）遥远的）点在未来，变得与大多数其他人在效率上相当。
回答by fu-chi
try this one
试试这个
SELECT COUNT(id)
FROM stats
GROUP BY EXTRACT(YEAR_MONTH FROM record_date)
EXTRACT(unit FROM date)function is better as less grouping is used and the function return a number value.
EXTRACT(unit FROM date)函数更好，因为使用较少的分组并且该函数返回一个数字值。
Comparison condition when grouping will be faster than DATE_FORMAT function (which return a string value). Try using function|field that return non-string value for SQL comparison condition (WHERE, HAVING, ORDER BY, GROUP BY).
分组时的比较条件将比 DATE_FORMAT 函数（返回字符串值）更快。尝试使用为 SQL 比较条件（WHERE、HAVING、ORDER BY、GROUP BY）返回非字符串值的函数|字段。
回答by dimazaid
I tried using the 'WHERE' statement above, I thought its correct since nobody corrected it but I was wrong; after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this:
我尝试使用上面的“WHERE”语句，我认为它是正确的，因为没有人纠正它，但我错了；经过一番搜索，我发现这是 WHERE 语句的正确公式，因此代码变成了这样：
SELECT COUNT(id)
FROM stats
WHERE YEAR(record_date) = 2009
GROUP BY MONTH(record_date)
回答by mr.baby123
If your search is over several years, and you still want to group monthly, I suggest:
如果你搜索了几年，还想按月分组，我建议：
version #1:
版本#1：
SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')
version #2(more efficient):
版本#2（更高效）：
SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)
I compared these versions on a big table with 1,357,918 rows (innodb), and the 2nd version appears to have better results.
我在一个有 1,357,918 行 ( innodb)的大表上比较了这些版本，第二个版本似乎有更好的结果。
version1(average of 10 executes): 1.404 seconds
version2(average of 10 executes): 0.780 seconds
version1 （平均执行 10 次）：1.404 秒
version2 （平均执行 10 次）：0.780 秒
(SQL_NO_CACHEkey added to prevent MySQL from CACHING to queries.)
（SQL_NO_CACHE添加密钥以防止 MySQL 缓存到查询。）
回答by Haijerome
If you want to group by date in MySQL then use the code below:
如果要在 MySQL 中按日期分组，请使用以下代码：
SELECT COUNT(id)
FROM stats
GROUP BY DAYOFMONTH(record_date)
Hope this saves some time for the ones who are going to find this thread.
希望这可以为将要找到此线程的人节省一些时间。
回答by Salman A
If you want to filter records for a particular year (e.g. 2000) then optimize the WHEREclause like this:
如果要过滤特定年份（例如 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.
Instead of:
代替：
WHERE YEAR(date_column) = 2000
-- average 0.132 sec.
The results were generated against a table containing 300k rows and index on date column.
结果是针对包含 300k 行和日期列索引的表生成的。
As for the GROUP BYclause, I tested the three variants against the above mentioned table; here are the results:
至于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.
The last one is the winner.
最后一位是赢家。
回答by Cees Timmerman
Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active:
完整而简单的解决方案，具有类似性能但更短、更灵活的替代方案，目前处于活动状态：
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')
回答by Faisal
You can do this simply Mysql DATE_FORMAT()function in GROUP BY. You may want to add an extra column for added clarity in some cases such as where records span several years then same month occurs in different years.Here so many option you can customize this. Please read this befor starting. Hope it should be very helpful for you. Here is sample query for your understanding
您可以在 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 ');
回答by user3019799
If you want to get a monthly statistics with row counts per month of each year ordered by latest month, then try this:
如果您想获得按最新月份排序的每年每月行数的月度统计数据，请尝试以下操作：
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
