php – MySQL选择特定周/月的最近2天的条目

发布时间：2020-12-13 22:52:52 所属栏目：PHP教程来源：网络整理

导读：我有2列,’create_time’表示帐户已注册,’last_play’表示帐户最后一次登录.我想选择在特定周/月内注册并且在此特定周/月的最后2天内有效的帐户. 以下是我如何在不考虑last_play(工作)的情况下选择上周的所有条目： SELECT COUNT(id) FROM account.account

我有2列,’create_time’表示帐户已注册,’last_play’表示帐户最后一次登录.我想选择在特定周/月内注册并且在此特定周/月的最后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());

解决方法

基于您的第一个工作查询,您可以使用MySQL函数WEEKDAY来识别星期六和星期日：

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

没关系闰年;-).或者自己手动再次合并.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!