MySQL/SQL:仅按Datetime列上的日期进行分组 [英] MySQL/SQL: Group by date only on a Datetime column
问题描述
具有一个表,该表的列如下:mydate DATETIME
...
Having a table with a column like: mydate DATETIME
...
我有一个查询,例如:
SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate;
这将按完整的datetime
进行分组,包括小时和分钟.我希望仅按日期YYYY/MM/DD
进行分组,而不按YYYY/MM/DD/HH/mm
进行分组.
This will group by the full datetime
, including hours and minutes. I wish to make the group by, only by the date YYYY/MM/DD
not by the YYYY/MM/DD/HH/mm
.
有人知道该怎么做吗?我仍然可以在我的代码中动态地执行此操作(因为我是atm),但是我正在清理垃圾代码,并且可以通过SQL来完成,我只是不知道如何:(.
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 :(.
推荐答案
将日期时间设置为日期,然后使用以下语法将GROUP BY设置为:
Cast the datetime to a date, then GROUP BY using this syntax:
SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);
或者您也可以按照@ orlandu63建议使用GROUP BY别名:
Or you can GROUP BY the alias as @orlandu63 suggested:
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.
这篇关于MySQL/SQL:仅按Datetime列上的日期进行分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!