MYSQL 上次登录和最近 3 个月的登录次数 [英] MYSQL last login and number of logins in last 3 months

问题描述

我正在尝试编写一个查询以将用户表连接到活动日志表并为每个用户返回以下内容:

I'm trying to write a query to join a user table to an activity logging table and return the following for each user:

A) 他们上次登录的时间.

A) The time they last logged in.

B) 最近 3 个月的登录次数.

B) The number of logins in the last 3 months.

这是我目前想到的:

SELECT A.UserID, COUNT( Activity ) AS Logins, MAX( TIME ) AS LastLogin  
FROM UserMaster A  
LEFT JOIN UserWebActivity B ON A.UserID = B.UserID  
AND Activity =  'Login'  
AND TIME BETWEEN DATE_SUB( NOW( ) , INTERVAL 3 MONTH ) AND NOW( )  
GROUP BY A.UserID  

这几乎有效，但它不会返回过去 3 个月内未登录的任何用户的最新登录信息.如何让 count() 和 max() 一起正常工作?

This almost works, but it doesn't return the latest login for any user that hasn't logged in within the last 3 months. How can I get count() and max() to work together properly?

推荐答案

先分别解决每个问题:

SELECT A.UserID, MAX(TIME) AS LastLogin  
FROM UserMaster A  
LEFT JOIN UserWebActivity B
ON A.UserID = B.UserID  
AND Activity = 'Login'  
GROUP BY A.UserID

SELECT A.UserID, COUNT(Activity) AS Logins
FROM UserMaster A  
LEFT JOIN UserWebActivity B ON A.UserID = B.UserID  
AND Activity = 'Login'  
AND TIME BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW( )  
GROUP BY A.UserID

单独测试它们以确保每个查询都能如您所愿，并在必要时进行调整.

Test them separately to ensure that each of these queries works as you want, and adjust them if necessary.

然后当您对它们都工作感到高兴时，将结果合并在一起:

Then when you are happy that they both work, join the results together:

SELECT T1.UserID, T1.LastLogin, T2.Logins
FROM
(
    SELECT A.UserID, MAX(TIME) AS LastLogin  
    FROM UserMaster A  
    LEFT JOIN UserWebActivity B
    ON A.UserID = B.UserID  
    AND Activity = 'Login'  
    GROUP BY A.UserID
) AS T1
JOIN
(
    SELECT A.UserID, COUNT(Activity) AS Logins
    FROM UserMaster A  
    LEFT JOIN UserWebActivity B
    ON A.UserID = B.UserID  
    AND Activity = 'Login'
    AND TIME BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW()
    GROUP BY A.UserID
) AS T2
ON T1.UserID = T2.UserID

这将允许 MySQL 为不同的查询充分利用索引.

This will allow MySQL to make best use of the indexes for the different queries.

这篇关于MYSQL 上次登录和最近 3 个月的登录次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！