如何在SQL Server中查询每个用户的最后登录时间及对应应用版本
你原有写法的问题
- 使用了
INNER JOIN,会过滤掉没有登录记录的用户(比如示例中的williams),不符合返回全部用户的需求 - SQL Server 要求
GROUP BY子句必须包含SELECT中所有未使用聚合函数的字段,你仅按user_name分组不符合语法规则 - 直接使用
GROUP BY无法直接关联最新登录日期对应的版本号,需要额外逻辑提取每个用户的最新登录记录
推荐写法(窗口函数实现,兼容性好逻辑清晰)
WITH UserLatestLogin AS ( SELECT user_id, created_at, version, -- 按用户分组,登录时间倒序排序,排名为1的就是该用户最新登录记录 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM LOG ) SELECT u.user_name, ISNULL(CONVERT(VARCHAR(10), l.created_at, 23), '-') AS last_login, ISNULL(l.version, '-') AS version FROM USERS u LEFT JOIN UserLatestLogin l ON u.user_id = l.user_id AND l.rn = 1 -- 仅保留每个用户的最新一条登录记录 ORDER BY u.user_id;
逻辑说明
- 先用CTE(公共表表达式)配合
ROW_NUMBER()窗口函数,给每个用户的登录记录按登录时间倒序编号,编号为1的就是该用户的最新登录记录 - 使用
LEFT JOIN关联用户表和处理后的登录记录,保证没有登录记录的用户也会被返回 - 用
ISNULL函数将空的登录日期、版本号替换为-,和你预期的输出格式一致
兼容旧版本SQL Server的替代写法(不用窗口函数)
如果你的SQL Server版本不支持窗口函数,可以用先取最大登录时间再关联回日志表的方式实现:
SELECT u.user_name, ISNULL(CONVERT(VARCHAR(10), max_l.last_login, 23), '-') AS last_login, ISNULL(l.version, '-') AS version FROM USERS u LEFT JOIN ( SELECT user_id, MAX(created_at) AS last_login FROM LOG GROUP BY user_id ) max_l ON u.user_id = max_l.user_id LEFT JOIN LOG l ON max_l.user_id = l.user_id AND max_l.last_login = l.created_at ORDER BY u.user_id;
注意:该写法如果存在同一个用户同一时间产生多条登录记录的情况,会返回多条重复用户数据,优先推荐使用窗口函数写法。
内容的提问来源于stack exchange,提问作者Zephyr
相关产品推荐
相关产品推荐

