我有表userlog(ID、Login(datetime)、UserID、IP、UserAgentgt…)
我想要的是为每个用户获取最新登录的所有数据。
Select UserID, MAX(Login), IP, UserAgent
From userlog
Group by UserID, IP, UserAgent
如果一个用户有多个IP和/或UserAgent,这将为我提供多个记录。。。并且我想要具有最大登录日期的行的数据。
我认为最简单的方法是使用窗口函数row_number()
:
select ul.UserID, ul.Login, ul.IP, ul.UserAgent
from (Select ul.*, row_number() over (partition by user_id order by login desc) as seqnum
From userlog ul
) ul
where seqnum = 1
此操作不必使用group by
。
Select u1.UserID, u1.Login, u1.IP, u1.UserAgent
From userlog u1
inner join
(
Select UserID, max(Login) as mLogin
From userlog
Group by UserID
) u2 on u1.UserID = u2.UserID and u2.mLogin = u1.Login
类似的东西
SELECT max.userid, max.maxlog, ul.ip, ul.useragent
FROM
(Select UserID, Max(Login) maxlog
From userlog
group by userID)
max
inner join userlog ul on ul.userid=max.userid and ul.login=max.maxlog