Retrieving Online Users By Login and Logout Record

问题:

• 

Consider UserLog Table as above. I save login or logout operation of users into this table. how can i retrieve Online users and Login Time?

回答1:

Something like this:

select * from UserLog l1
where Operation = 'Enter' and 
      not exists(select * from UserLog l2 
                 where l1.user = l2.user and 
                       l2.Operation = 'Exit' and 
                       l2.Time > l1.Time)

回答2:

I think this should work for you:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY UL.id ORDER BY UL.Time DESC) AS RN, UL.*
    FROM dbo.UserLog AS UL
    ) AS T
WHERE T.RN = 1
    AND T.Operation = 'Enter';

Just partition your table by UserID and sort by Last action time - if it's Enter, then he must be online.