MySQL/SQL：仅在日期时间列上按日期分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/366603/
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/SQL: Group by date only on a Datetime column
提问by fmsf
Having a table with a column like: mydate DATETIME...
有一个带有如下列的表：mydate DATETIME...
I have a query such as:
我有一个查询，例如：
SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate;
This will group by the full datetime, including hours and minutes. I wish to make the group by, only by the date YYYY/MM/DDnot by the YYYY/MM/DD/HH/mm.
这将按完整分组datetime，包括小时和分钟。我希望按日期分组，而YYYY/MM/DD不是按日期分组YYYY/MM/DD/HH/mm。
Anyone know how to do this? I can still do it (as I am atm), dynamically in my code, but I'm cleaning trash code and this can be made through the SQL I just can't find out how :(.
有人知道怎么做吗？我仍然可以在我的代码中动态地做到这一点（因为我是 atm），但是我正在清理垃圾代码，这可以通过 SQL 完成，我只是不知道如何:(。
回答by Michael Haren
Cast the datetime to a date, then GROUP BY using this syntax:
将日期时间转换为日期，然后使用以下语法 GROUP BY：
SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);
Or you can GROUP BY the alias as @orlandu63 suggested:
或者您可以按照@orlandu63 的建议按别名分组：
SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;
Though I don't think it'll make any difference to performance, it is a little clearer.
虽然我认为它不会对性能产生任何影响，但它更清晰一些。
回答by Richard Merchant
I found that I needed to group by the month and year so neither of the above worked for me. Instead I used date_format
我发现我需要按月份和年份分组，所以上述两种方法都不适合我。相反，我使用了 date_format
SELECT date
FROM blog
GROUP BY DATE_FORMAT(date, "%m-%y")
ORDER BY YEAR(date) DESC, MONTH(date) DESC
回答by moo
Or:
或者：
SELECT SUM(foo), DATE(mydate) mydate FROM a_table GROUP BY mydate;
More efficient (I think.) Because you don't have to cast mydate twice per row.
更有效（我认为）。因为您不必每行两次投射 mydate。
回答by RaK Chowdary
SELECT SUM(No), HOUR(dateofissue)
FROM tablename
WHERE dateofissue>='2011-07-30'
GROUP BY HOUR(dateofissue)
It will give the hour by sum from a particular day!
它将从特定的一天按总和给出小时！
