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屋！