脚本宝典收集整理的这篇文章主要介绍了php – MySQL选择特定周/月的最近2天的条目,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
以下是我如何在不考虑last_play(工作)的情况下选择上周的所有条目:
SELECT COUNT(id) From account.account WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(Now()) - 1 AND YEAR(create_time) = YEAR(Now());
这是我上周的当前查询,它不起作用:
SELECT COUNT(id) From account.account WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(Now()) - 1 AND YEAR(create_time) = YEAR(Now()) AND DATE(last_play) BETWEEN ADDDATE(DATE(DATE_Sub(Now(),INTERVAL 1 WEEK)),INTERVAL 1 - DAYOFWEEK(DATE(Now())) DAY) AND DATE(Now());
SELECT COUNT(id) FROM account.account WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(Now()) - 1 AND YEAR(create_time) = YEAR(Now()) AND WEEKOFYEAR(last_play) = WEEKOFYEAR(create_time) //last_play is in the same week as create_time AND WEEKDAY(last_play) IN (5,6); //wekkday is saturday or sunday
编辑:几个月来,你基本上做同样的事情,但用DAYOFMONTH代替WEEKOFYEAR到MONTH和WEEKDAY.在给定月份的最后两天,您可以通过为所有可能的案例进行拼贴手动找到:
SELECT COUNT(id) FROM account.account WHERE MONTH(create_time) = MONTH(Now()) - 1 AND YEAR(create_time) = YEAR(Now()) AND MONTH(last_play) = MONTH(create_time) //last_play is in the same MONTH as create_time AND (DAYOFMONTH(last_play) IN (30,31) AND MONTH(last_play) IN (1,3,5,7,8,10,12) OR DAYOFMONTH(last_play) IN (29,30) AND MONTH(last_play) IN (4,6,9,11) OR DAYOFMONTH(last_play) IN (27,28) AND MONTH(last_play) IN (2))
没关系闰年;-).或者自己手动再次合并.
以上是脚本宝典为你收集整理的php – MySQL选择特定周/月的最近2天的条目全部内容,希望文章能够帮你解决php – MySQL选择特定周/月的最近2天的条目所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。