假设他们已经每次登录,则可以使用having
子句:
SELECT user_id,email,max(logged)
FROM logins
GROUP BY user_id,email
HAVING MAX(logged) <= DATE_SUB(curdate(),INTERVAL 1 WEEK);
如果要包括所有用户,甚至包括那些从未登录过的用户,我建议您使用关联的子查询和HAVING
。假设您有一个用户表:
select u.*,(select MAX(.logged)
FROM logins l
WHERE l.user_id = u.user_id
) as most_recent_logged
from users u
having most_recent_logged <= DATE_SUB(curdate(),INTERVAL 1 WEEK);
请注意,HAVING
子句的这种使用是MySQL扩展。即使在非聚合查询中,它也允许您按列别名进行过滤。
,
我发现了如何包括尚未登录的用户。首先,您必须首先选择所有用户都已登录的表,而不是已登录的用户,然后最后您将拥有“ OR COUNT(most_recent_logged)= 0”:
SELECT u.email,u.enabled,l.access,l.logged,u.first_name,u.surname,u.telephone_no,s.farm_id,s.farmer,(SELECT MAX(logged)
FROM logins l
WHERE l.user_id = u.id) as most_recent_logged
FROM users u
LEFT JOIN logins l on u.id = l.user_id
LEFT JOIN settings s on u.id = s.user_id
WHERE farmer = 1
GROUP BY u.id,u.email DESC
HAVING most_recent_logged <= DATE_SUB(curdate(),INTERVAL 1 WEEK)
OR COUNT(most_recent_logged) = 0
本文链接:https://www.f2er.com/3121973.html