mysql查询一个月未登录_mysql – 在过去n个月内未登录的用户

以下查询将返回从现在起10个月内的每个月差异的不同ID的计数.

SELECT

12 * (YEAR(CURDATE()) - YEAR(last_login)) + (MONTH(CURDATE()) - MONTH(last_login)) AS months,

count(DISTINCT id) cnt

FROM

tmp_last_login

WHERE last_login >= DATE_SUB(CURDATE(), INTERVAL 10 MONTH)

GROUP BY months;

要么

您也可以使用PERIOD_DIFF函数.

SELECT

PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'),DATE_FORMAT(last_login,'%Y%m')) AS months,

count(DISTINCT id) cnt

FROM

tmp_last_login

WHERE last_login >= DATE_SUB(CURDATE(), INTERVAL 10 MONTH)

GROUP BY months;

注意：如果同一用户有多个last_login条目,则需要找到每个用户的最长上次登录时间,然后完成剩下的工作.

SELECT

PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'),DATE_FORMAT(t.max_last_login,'%Y%m')) AS months,

count(t.id) cnt

FROM

(

SELECT

id,

MAX(last_login) max_last_login

FROM tmp_last_login

GROUP BY id

) AS t

WHERE t.max_last_login >= DATE_SUB(CURDATE(), INTERVAL 10 MONTH)

GROUP BY months;