mysql以datetime的年月分组,MySQL / SQL：仅在Datetime列上按日期分组

最新推荐文章于 2023-03-23 10:16:40 发布

weixin_39805539

最新推荐文章于 2023-03-23 10:16:40 发布

阅读量385

点赞数

文章标签： mysql以datetime的年月分组

该博客讨论了如何在SQL查询中按日期而不是完整日期时间进行分组。通过将DATETIME字段转换为DATE，可以实现按YYYY/MM/DD日期分组。解决方案包括使用DATE()函数对日期时间字段进行转换，并在GROUP BY子句中使用转换后的日期。

摘要由CSDN通过智能技术生成

Having a table with a column like: 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/DD not by the 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 :(.

解决方案

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);

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.