使用 MySQL 获取一周开始日期
如果我有这样的 MySQL 查询，每周对词频进行求和：
SELECT
SUM(`city`),
SUM(`officers`),
SUM(`uk`),
SUM(`wednesday`),
DATE_FORMAT(`dateTime`, '%d/%m/%Y')
FROM myTable
WHERE dateTime BETWEEN '2011-09-28 18:00:00' AND '2011-10-29 18:59:00'
GROUP BY WEEK(dateTime)
MySQL 给出的结果采用 dateTime 列的第一个值，在本例中为 28/09/2011，恰好是星期六。
是否可以调整 MySQL 中的查询以显示一周开始的日期，即使没有可用数据，以便对于上述内容，2011-09-28 将替换为 2011/09/26？也就是说，一周的开始日期是星期一。或者在查询运行后以编程方式调整日期会更好吗？
日期时间列的格式为 2011/10/02 12:05:00
If I have MySQL query like this, summing word frequencies per week:
SELECT
SUM(`city`),
SUM(`officers`),
SUM(`uk`),
SUM(`wednesday`),
DATE_FORMAT(`dateTime`, '%d/%m/%Y')
FROM myTable
WHERE dateTime BETWEEN '2011-09-28 18:00:00' AND '2011-10-29 18:59:00'
GROUP BY WEEK(dateTime)
The results given by MySQL take the first value of column dateTime, in this case 28/09/2011 which happens to be a Saturday.
Is it possible to adjust the query in MySQL to show the date upon which the week commences, even if there is no data available, so that for the above, 2011-09-28 would be replaced with 2011/09/26 instead? That is, the date of the start of the week, being a Monday. Or would it be better to adjust the dates programmatically after the query has run?
The dateTime column is in format 2011/10/02 12:05:00
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
可以在 SQL 中执行此操作，但最好在程序代码中执行此操作，因为它会更有效且更容易。另外，虽然 MySQL 接受您的查询，但它不太有意义 - 当您按
分组时， select 的字段列表中有。这意味着数据库引擎必须从当前组（周）中为每行选择随机日期。即考虑您有DATE_FORMAT(dateTime, '%d/%m/%Y')>WEEK(日期时间)27.09.2011、28.09.2011和29.09.2011的记录 - 它们都落在同一周，所以在决赛中结果集仅为这三条记录生成一行。现在应该为 DATE_FORMAT() 调用选择这三个日期中的哪个日期？如果查询中有ORDER BY，答案会稍微简单一些，但使用字段列表中不在GROUP BY中的字段/表达式仍然不太有意义code> 或哪些不是聚合。您确实应该在选择列表中返回周数（而不是 DATE_FORMAT 调用），然后在代码中计算开始日期和结束日期。It is possible to do it in SQL but it would be better to do it in your program code as it would be more efficient and easier. Also, while MySQL accepts your query, it doesn't quite make sense - you have
DATE_FORMAT(dateTime, '%d/%m/%Y')in select's field list while you group byWEEK(dateTime). This means that the DB engine has to select random date from current group (week) for each row. Ie consider you have records for27.09.2011,28.09.2011and29.09.2011- they all fall onto same week, so in the final resultset only one row is generated for those three records. Now which date out of those three should be picked for the DATE_FORMAT() call? Answer would be somewhat simpler if there isORDER BYin the query but it still doesn't quite make sense to use fields/expressions in the field list which aren't inGROUP BYor which aren't aggregates. You should really return the week number in the select list (instead of DATE_FORMAT call) and then in your code calculate the start and end dates from it.