基于Event列值合并TimeLog多行数据的SQL实现需求

需求说明 #

基于Event列的取值（仅包含Login和Logout两种）将TimeLog表中的多行数据合并为一行，同时满足：

• 结果按时间顺序排序
• 边界处理：若Login缺失则用Logout值填充，若Logout缺失则用Login值填充，二者缺其一则取值相同

现有查询及结果 #

现有查询语句 #

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

查询结果 #

期望合并结果 #

边界处理示例 #

边界数据 #

期望边界处理结果 #

本人尝试的代码 #

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' 

解决方案 #

以下是适配需求的SQL语句（以SQL Server为例）：

WITH RankedEvents AS (
    SELECT 
        EmployeeID,
        RoomID,
        Event,
        EventDate,
        -- 给每个Login及对应Logout分配同组ID
        GroupID = SUM(CASE WHEN Event = 'Login' THEN 1 ELSE 0 END) OVER (
            PARTITION BY EmployeeID ORDER BY EventDate
        )
    FROM TimeLog
    WHERE EmployeeID = '107733'
      AND EventDate BETWEEN '2020-02-26 00:00:00' AND '2020-02-26 23:59:59'
)
SELECT 
    EmployeeID,
    RoomID,
    -- 缺失Login时用组内时间填充
    Login = COALESCE(MAX(CASE WHEN Event = 'Login' THEN EventDate END), MAX(EventDate)),
    -- 缺失Logout时用组内时间填充
    Logout = COALESCE(MAX(CASE WHEN Event = 'Logout' THEN EventDate END), MAX(EventDate))
FROM RankedEvents
GROUP BY EmployeeID, RoomID, GroupID
-- 按组的最早时间排序，保证结果顺序正确
ORDER BY MIN(EventDate);

逻辑说明 #

1. 用窗口函数生成GroupID：每遇到一个Login就递增分组ID，确保每个Login和后续对应的Logout属于同一组
2. 分组聚合时，用CASE提取Login/Logout时间，通过COALESCE处理缺失情况，补全对应值
3. 最后按组的最早时间排序，匹配需求中的时间顺序要求

内容的提问来源于stack exchange，提问作者Linc Abela