php - MySQL选择特定周/月的最后2天中的条目

我有两列，“create_time”是该帐户已注册的时间，“last_play”是该帐户上次登录的时间。我想选择已在特定周/月中注册的帐户，并且在该特定周/月中的最后两天内处于事件状态的帐户。

这是我在不考虑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

这将为您提供在他们注册的同一周的周六或周日处于事件状态的条目。

编辑:几个月来，您基本上都做同样的事情，但是用WEEKOFYEAR替换MONTH，用WEEKDAY替换DAYOFMONTH。您可以通过咨询所有可能的情况来手动找到给定月份的最后两天:

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

没关系，year年；-)。或自己手动重新合并。