MySQL在上一个工作周获取记录 [英] MySQL Getting Records over the Last Working Week

问题描述

我正在使用

 WHERE WEEK(curdate,3)-WEEK(date)=1 

获取上一个工作周(周一至周日)记录的表的所有行.全年效果不错，但在一月份会返回0，所以我不得不接受

to get all lines of a table that are recorded on the last working week (Monday to Sunday). Works well the whole year, but in January that will return 0 for some time, so I have to take

 WHERE WEEK(curdate,3)+53-WEEK(date)

在这种情况下

.我不能使用OR，因为当同时存在时，它将同时获取当前年份信息和去年信息.我已经尝试过COALESCE和IFNULL方法:

in that case. I can't use OR because it would take both current year info and last year info when both exist. I already tried COALESCE and IFNULL methods:

 WHERE COALESCE(
     (WEEK(curdate(),3)-WEEK(date,3) = 1),
     (WEEK(curdate(),3)+53-WEEK(date,3) = 1
 )

全年有效，但IFNULL为true时无法解决问题. 请给我有关有效处理此案的建议.

It works well for the whole year but doesn't manage the case when IFNULL is true. Please give me advice on effective handling of this case.

推荐答案

您可以使用以下方法获取当前星期几的日期:

You can get the date of the Monday of the current week using:

select SUBDATE(curdate(), WEEKDAY(curdate()));

，然后在您的谓词中使用本周星期一的日期和上一周的星期一:

and then use the date of the Monday of this week and the Monday of the previous week in your predicate:

WHERE date >= SUBDATE(curdate(), WEEKDAY(curdate())) - interval 1 week
AND date < SUBDATE(curdate(), WEEKDAY(curdate()))

这篇关于MySQL在上一个工作周获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！