我想根据列的值将多行分组为一行。
给定此数据:
SELECT EmployeeID, RoomId , [Event], EventDate
FROM TimeLog
WHERE EmployeeID = '107733'
AND EventDate BETWEEN '2020/02/26 00:00:00' AND '2020/02/26 23:59:59'
ORDER BY EventDate
EmployeeID|RoomID|Event|EventDate
-|-|
107733|05-27F|登录|2020-02-26 07:02:00
107733|05-27F|注销|2020-02-26 08:38:00
107733|05-25F|登录|2020-02-26 08:39:00
107733|05-25F|注销|2020-02-26 08:51:00
107733|05-27F|登录|2020-02-26 08:52:00
107733|05-27F|注销|2020-02-26 12:00:00
因此,基于Event列的值,该列包含两个可能的值-Login和Logout,我想将两行合并为一行,结果如下所示:
EmployeeID|RoomID|Login|注销
-|-|
107733|05-27F|2020-02-26 07:02:00|2020-02-26 08:38:00
107733|05-25F|2020-02-26 08:39:00|2020-02-26 08:51:00
107733|05-27F|2020-02-26 08:52:00|2020-02-26 12:00:00
其他要求(如果可能):
- 应按时间顺序排序
- (边框大小写)如果“Login”丢失或没有值,它应该得到Logout的值,如果“Logout”没有值,它应该得到“Login”字段的值。因此,如果缺少一个,登录和注销将具有相同的值。
EmployeeID|RoomID|Event|EventDate
-|-|
107733|05-27F|登录|2020-02-26 07:02:00
107733|05-25F|注销|2020-02-26 08:38:00
请注意,在本例中,这发生在不同的“Room”中。在05-27F上没有注销,而在05-25F上没有登录。预期结果为:
EmployeeID|RoomID|Login|注销
-|-|
107733|05-27F|2020-02-26 07:02:00|2020-02-26 07:02:00
107733|05-25F|2020-02-26 08:38:00|2020-02-26 08:38:00
我试图解决这个问题:
SELECT
EmployeeID,
RoomID,
'Login' = (SELECT TOP 1 EventDate
FROM TimeLog li
WHERE li.EmployeeID = tl.EmployeeID
AND li.RoomID = tl.RoomID
AND li.[Event] = 1),
'Logout' = (SELECT TOP 1 EventDate
FROM TimeLog lo
WHERE lo.EmployeeID = tl.EmployeeID
AND lo.RoomID = tl.RoomID
AND lo.[Event] = 2)
FROM
TimeLog tl
WHERE
tl.EmployeeID = '107733'
AND tl.EventDate BETWEEN '2020/02/26 00:00:00' AND '2020/02/26 23:59:59'
3条答案
按热度按时间sirbozc51#
我相信这应该能达到你想要的效果:DBFiddle Example
这是通过对每个员工/房间组合的事件排序来实现的。
如果在登录之后立即出现注销(即,序列号(R)或注销事件比登录事件的序列号多1),则两者是相关的&放在同一行中。
如果您获得的任何行的登录没有连续注销,或者没有之前登录的注销,则这些行被视为具有丢失的数据,因此您在相应的注销时间/登录时间列中得到NULL。
希望这是有意义的&有帮助--如果你需要更深入的解释,请告诉我。
用于创建演示数据的代码
输出示例
LoginDate|LogoutDate|EmployeeId_|RoomID_
-|-|
107733|05-27F|2020-02-26 07:02:00.000|2020-02-26 08:38:00.000
2020-02-26 08:39:00.000|2020-02-26 08:51:00.000|2020|05-25F
107733|05-25X|2020-02-27 08:39:00.000|2020-02-27 08:39:00.000
2020-02-27 08:39:00.000|2020-02-27 08:51:00.000|2020|05-25F
107733-02-27 08:51:00.000|2020-02-27 08:51:00.000|2020-02-27 08:51:25
跟进问题答案
试试这个:DB Fiddle
它的工作原理是像以前一样为每个登录提供一个连续的ID;对于注销,它计算在它之前出现的登录次数。然后,可以将这些数字组合在一起,以确保最新的登录始终与其最早的相应注销配对;而不会丢失任何记录。
即
priorLoginCount
将为0
。loginNo
为1
。在该登录之后但在下一次登录之前发生的所有注销记录的priorLoginCount
为1
。loginNo
为2
。在该登录之后但在第三次登录之前发生的所有注销记录的priorLoginCount
为2
。loginNo
值将高于任何注销记录的priorLoginCount
值。因此,我们可以根据这些值将我们的记录配对。
nx7onnlm2#
如果您坚定地选择了SQL Server,那么Pivot也是一个选择:
w6lpcovy3#
也许是这样的:
更新:每天第一次登录和最后一次注销。