如何计算同一用户出入门时间差并优化SQL考勤报表

我来帮你搞定这个员工刷卡记录的报表优化问题！咱们的核心需求很明确：从可能存在忘刷、尾随的混乱刷卡记录里，把每个员工的进门、出门时间配对，算出实际在房间的停留时长，同时优化原有查询的输出格式，新增时长列。

先拆解原SQL的局限 #

原查询用MAX(e.LoggedTime)分组，只能拿到每个用户、每个门、每个事件类型的最晚刷卡时间，没法把同一个用户的进门和出门记录关联起来，自然没法计算时长。所以咱们得换个思路，用窗口函数+自连接来配对记录。

优化后的SQL查询 #

假设咱们的EventTypes里，进门事件的名称是'进门'、出门是'出门'（你可以替换成实际的EventType名称/ID），下面是优化后的代码：

WITH UserSwipeRecords AS (
    SELECT 
        u.userid,
        u.[FirstName] + ' ' + u.[LastName] AS EmployeeName,
        et.name AS EventDescription,
        e.LoggedTime,
        d.name AS DoorName,
        -- 给每个用户、每个门的刷卡记录按时间排序，方便配对进出
        ROW_NUMBER() OVER (PARTITION BY u.userid, d.doorid ORDER BY e.LoggedTime) AS SwipeSeq
    FROM [Users] AS u 
    LEFT JOIN [Events] AS e ON e.RecordIndex1 = u.UserID 
    LEFT JOIN [EventTypes] AS et ON e.EventTypeID = et.EventTypeID 
    JOIN [Doors] AS d ON e.RecordIndex2 = d.DoorID 
    WHERE 
        e.LoggedTime > CONVERT(DATE, GETDATE()) 
        AND d.doorid IN (32, 50, 42, 51, 33)
        AND et.name IN ('进门', '出门') -- 只筛选进出事件，排除无关记录
)
SELECT 
    in_rec.userid,
    in_rec.EmployeeName,
    in_rec.DoorName,
    in_rec.LoggedTime AS EntryTime, -- 进门时间
    out_rec.LoggedTime AS ExitTime, -- 出门时间
    -- 计算停留时长，这里用秒举例，可换成MINUTE/HOUR等单位
    DATEDIFF(SECOND, in_rec.LoggedTime, out_rec.LoggedTime) AS StayDurationSeconds
FROM UserSwipeRecords in_rec
-- 自连接配对：进门记录的下一条就是对应的出门记录
LEFT JOIN UserSwipeRecords out_rec 
    ON in_rec.userid = out_rec.userid 
    AND in_rec.DoorName = out_rec.DoorName 
    AND in_rec.SwipeSeq + 1 = out_rec.SwipeSeq
    AND in_rec.EventDescription = '进门' 
    AND out_rec.EventDescription = '出门'
WHERE in_rec.EventDescription = '进门' -- 只展示有进门记录的条目
ORDER BY in_rec.EmployeeName, in_rec.EntryTime;

关键逻辑说明 #

• CTE UserSwipeRecords：先整理所有有效刷卡记录，用ROW_NUMBER()给每个用户、每个门的记录按时间生成序列号SwipeSeq，为后续配对做准备。
• 自连接配对：把进门记录和它的下一条同用户、同门的出门记录关联起来，精准匹配对应的进出时间。
• 异常处理：如果员工只有进门没有出门，ExitTime和StayDurationSeconds会显示为NULL，你可以用ISNULL()函数标注为'未刷卡出门'这类提示。
• 效率优化：如果EventType用ID区分（比如进门是1、出门是2），可以把et.name IN ('进门','出门')换成e.EventTypeID IN (1,2)，查询速度会更快。

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