仅在日期时间列上按日期分组

2021-11-20 00:00:00 sql mysql

有一个包含如下列的表: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.

如何做到这一点?

推荐答案

将日期时间转换为日期，然后使用以下语法进行 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 的建议对别名进行分组:

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.

相关文章