从单个表返回最近登录的设备列表

Question

我需要修复一个SQL查询，它只返回最近登录的用户，而不是记录表中的所有登录用户。我使用的是SQL Server2008 R2。

表格如下所示：

ID  StoreID DeviceTimeStamp CreatedBy   CreatedDate UpdatedBy   UpdatedDate action  PersonID    Description
----------------------------------------------------------------------------------------------------------------------
56  82  2/11/13 8:43    Handheld Login  2/11/13 8:43    Handheld Login  2/11/13 8:43    1   97  Login
57  82  2/11/13 8:45    Handheld Login  2/11/13 8:45    Handheld Login  2/11/13 8:45    1   97  Inventory Recorded
58  82  2/11/13 8:59    Handheld Login  2/11/13 8:59    Handheld Login  2/11/13 8:59    1   97  Logout
59  82  2/11/13 9:12    Handheld Login  2/11/13 9:12    Handheld Login  2/11/13 9:12    1   97  Login
60  82  2/11/13 9:14    Handheld Login  2/11/13 9:14    Handheld Login  2/11/13 9:14    1   97  Issue Reported
61  82  2/11/13 9:14    Handheld Login  2/11/13 9:14    Handheld Login  2/11/13 9:14    1   97  Issue Reported
62  82  2/11/13 9:22    Handheld Login  2/11/13 9:22    Handheld Login  2/11/13 9:22    1   97  Logout
63  82  2/11/13 10:42   Handheld Login  2/11/13 10:42   Handheld Login  2/11/13 10:42   1   97  Login
64  82  2/11/13 10:42   Handheld Login  2/11/13 10:42   Handheld Login  2/11/13 10:42   1   97  Logout
65  82  2/11/13 11:18   Handheld Login  2/11/13 11:18   Handheld Login  2/11/13 11:18   1   97  Login
66  82  2/11/13 11:18   Handheld Login  2/11/13 11:18   Handheld Login  2/11/13 11:18   1   97  Logout
67  NULL    2/11/13 11:19   Handheld Login  2/11/13 11:19   Handheld Login  2/11/13 11:19   1   97  Travel Time
68  NULL    2/11/13 11:19   Handheld Login  2/11/13 11:19   Handheld Login  2/11/13 11:19   1   97  Travel Time (Logout)
69  NULL    2/11/13 11:19   Handheld Login  2/11/13 11:19   Handheld Login  2/11/13 11:19   1   97  Absent
70  NULL    2/11/13 11:20   Handheld Login  2/11/13 11:20   Handheld Login  2/11/13 11:20   1   97  Absent (Logout)
71  82  2/11/13 11:46   Handheld Login  2/11/13 11:46   Handheld Login  2/11/13 11:46   1   97  Login
72  82  2/11/13 13:14   Handheld Login  2/11/13 13:14   Handheld Login  2/11/13 13:14   1   97  Logout
73  NULL    2/11/13 13:14   Handheld Login  2/11/13 13:14   Handheld Login  2/11/13 13:14   1   97  Travel Time
74  NULL    2/11/13 13:17   Handheld Login  2/11/13 13:17   Handheld Login  2/11/13 13:17   1   97  Travel Time (Logout)
75  82  2/11/13 13:17   Handheld Login  2/11/13 13:17   Handheld Login  2/11/13 13:17   1   97  Login
76  82  2/12/13 11:09   Handheld Login  2/12/13 11:09   Handheld Login  2/12/13 11:09   1   97  Login
77  82  2/12/13 11:10   Handheld Login  2/12/13 11:10   Handheld Login  2/12/13 11:10   1   97  Logout
78  82  2/13/13 15:27   Handheld Login  2/13/13 15:27   Handheld Login  2/13/13 15:27   1   97  Login
79  52  2/13/13 15:49   Handheld Login  2/13/13 15:49   Handheld Login  2/13/13 15:49   1   97  Login

从该表中，我正在尝试获取有关当前登录到哪个位置的人员的最新信息。我使用的脚本只返回最后登录的人的信息，而不返回其他人的信息。从上面看，应该有1个人(PersonID)登录到2个位置(StoreID)。

我尝试删除DISTINCT，并获得该表中的行数的最后登录人员的列表。(错误)。

我不太擅长聚合函数，所以非常欢迎任何帮助修复这个查询的人。

现有代码- SQL

select DISTINCT Lin.ID,
            Lin.PersonID, 
            Lin.StoreID, 
            MAX(Lin.DeviceTimeStamp)as DeviceTimeStamp, 
            Lin.CreatedBy, 
            Lin.CreatedDate,
            Lin.UpdatedBy,
            Lin.UpdatedDate, 
            Lin.action,
             Lin.description
from 
    (select ID, 
        PersonID, 
        StoreID, 
        MAX(DeviceTimeStamp) as DeviceTimeStamp, 
        CreatedBy,
        CreatedDate, 
        UpdatedBy, 
        UpdatedDate, 
        action, 
        Description
     from KF_EDC_Logging 
    where 
        Description = 'Login' 
    group by PersonID, 
        StoreID,
        DeviceTimeStamp, 
        ID, 
        CreatedBy, 
        CreatedDate,
         UpdatedBy, 
        UpdatedDate,
         action, 
        description ) Lin 
full outer join
    (select PersonID, 
        DeviceTimeStamp as DateTimeOut, 
        CreatedBy,
        CreatedDate, 
        UpdatedBy, 
        UpdatedDate, 
        action, 
        description 
    from KF_EDC_Logging 
    where 
        Description = 'Logout')  out 
    on Lin.PersonID = out.PersonID 
Where
    (out.DateTimeOut is null OR Lin.DeviceTimeStamp in 
        (Select MAX(KF_EDC_Logging.DeviceTimeStamp) from KF_EDC_Logging))
Group BY Lin.DeviceTimeStamp, 
        Lin.PersonID, 
        Lin.StoreID, 
        out.DateTimeOut, 
        Lin.ID, 
        Lin.CreatedBy, 
        Lin.CreatedDate, 
        Lin.UpdatedBy,
        Lin.UpdatedDate, 
        Lin.action,
        Lin.description

Answer 1

这是我的尝试。它使用公用表表达式来计算上次登录的日期，然后将结果连接回其余数据。

WITH LogIn
AS
(
  SELECT PersonID, StoreID, MAX(DeviceTimeStamp) AS [DeviceTimeStamp]
  FROM KF_EDC_Logging
  WHERE Description = 'Login'
  GROUP BY PersonID, StoreID
),
LogOut
AS
(
  SELECT PersonID, StoreID, MAX(DeviceTimeStamp) AS [DeviceTimeStamp]
  FROM KF_EDC_Logging
  WHERE Description = 'Logout'
  GROUP BY PersonID, StoreID
)
SELECT K.ID, K.StoreID, LogIn.[DeviceTimeStamp], K.CreatedBy,
  K.CreatedDate, K.UpdatedBy, K.UpdatedDate, K.action,
  K.PersonID, K.Description
FROM LogIn
LEFT JOIN LogOut 
  ON LogIn.PersonID = LogOut.PersonID
  AND LogIn.StoreID = LogOut.StoreID
INNER JOIN KF_EDC_Logging AS K
  ON LogIn.PersonID = K.PersonID
  AND LogIn.StoreID = K.StoreID
  AND LogIn.DeviceTimeStamp = K.DeviceTimeStamp
WHERE (LogIn.[DeviceTimeStamp] > LogOut.[DeviceTimeStamp])
  OR (LogIn.[DeviceTimeStamp] IS NOT NULL AND LogOut.[DeviceTimeStamp] IS NULL)

SQL Fiddle here