如何在SQL内连接查询中仅显示用户最新登录记录

因为usrlogid随每次登录递增，所以每个用户的最大usrlogid对应的就是最新登录记录，这里提供两种常用修改方式：

方法一：使用窗口函数（推荐，逻辑清晰） #

利用ROW_NUMBER()窗口函数按用户分组，对每个用户的登录记录按usrlogid降序排序，只保留排序后行号为1的记录：

SELECT 
    Login_Date,
    User_Name,
    User_ID,
    Log_ID,
    Active
FROM (
    SELECT  
        usrlogs.serverlogintime AS Login_Date,
        usrlogs.usrname         AS User_Name,
        usrlogs.usrid           AS User_ID,
        usrlogs.usrlogid        AS Log_ID,
        users.status            AS Active,
        -- 按用户分组，按Log_ID降序排，给每条记录标行号
        ROW_NUMBER() OVER (PARTITION BY usrlogs.usrid ORDER BY usrlogs.usrlogid DESC) AS rn
    FROM   usrlogs
           INNER JOIN users
                   ON usrlogs.usrid = users.uid
    -- 直接用日期比较，比DATE_FORMAT更高效，避免索引失效
    WHERE  usrlogs.ServerLoginTime >= '2022-01-01' 
           AND users.status = '0'
) AS temp
-- 只保留每个用户的第一条（最新）记录
WHERE rn = 1;

方法二：使用子查询获取每个用户的最大Log_ID #

先查询出每个用户符合条件的最大usrlogid，再关联原表获取完整记录：

SELECT 
    usrlogs.serverlogintime AS Login_Date,
    usrlogs.usrname         AS User_Name,
    usrlogs.usrid           AS User_ID,
    usrlogs.usrlogid        AS Log_ID,
    users.status            AS Active
FROM   usrlogs
       INNER JOIN users
               ON usrlogs.usrid = users.uid
       -- 关联子查询，获取每个用户的最大Log_ID
       INNER JOIN (
           SELECT usrid, MAX(usrlogid) AS max_log_id
           FROM usrlogs
           WHERE ServerLoginTime >= '2022-01-01'
           GROUP BY usrid
       ) AS max_logs
           ON usrlogs.usrid = max_logs.usrid
           AND usrlogs.usrlogid = max_logs.max_log_id
WHERE  users.status = '0';

额外优化提示 #

原查询中DATE_FORMAT(ServerLoginTime,'%Y-%m-%d') >= '2022-01-01'会导致ServerLoginTime字段的索引失效（如果有索引的话），改成ServerLoginTime >= '2022-01-01'更高效，因为数据库可以直接利用索引快速筛选数据。

内容的提问来源于stack exchange，提问作者M P