SQL Server跨天考勤查询问题：无法正确匹配上下班打卡时间

针对你遇到的跨天打卡关联错误，核心思路是为每个员工的签到记录，匹配其之后最早的同员工签退记录，不管签退是否跨天。以下提供两种适合SQL Server的解决方案：

方案一：用OUTER APPLY匹配后续签退（直观易理解） #

这种方式逻辑清晰，适合新手，能精准找到每个签到对应的下一个签退：

SELECT
    a.E_ID,
    a.Attend_date AS 签到日期,
    a.E_Time AS 签到时间,
    b.Attend_date AS 签退日期,
    b.E_Time AS 签退时间
FROM ATT a
OUTER APPLY (
    -- 为当前签到记录找同员工、时间更晚的第一个签退
    SELECT TOP 1 *
    FROM ATT b
    WHERE b.E_ID = a.E_ID
      AND b.Punch_mode = 2
      -- 合并日期和时间，解决跨天的时间比较问题
      AND CONVERT(DATETIME, b.Attend_date) + CONVERT(TIME, b.E_Time) 
          > CONVERT(DATETIME, a.Attend_date) + CONVERT(TIME, a.E_Time)
    ORDER BY CONVERT(DATETIME, b.Attend_date) + CONVERT(TIME, b.E_Time) ASC
) b
WHERE a.Punch_mode = 1 -- 只处理签到记录
ORDER BY a.E_ID, CONVERT(DATETIME, a.Attend_date) + CONVERT(TIME, a.E_Time)

关键说明： #

• 通过CONVERT(DATETIME, Attend_date) + CONVERT(TIME, E_Time)把日期和时间合并成完整的datetime，解决跨天情况下单纯比较日期或时间不准确的问题
• OUTER APPLY会为每个签到记录单独查询符合条件的签退，确保配对准确

方案二：用窗口函数LEAD（高效简洁） #

如果数据量较大，窗口函数的性能更优，通过预排序获取后续打卡记录：

WITH PunchRecords AS (
    SELECT
        E_ID,
        Attend_date,
        E_Time,
        Punch_mode,
        -- 按员工分组、打卡时间排序，获取下一条打卡的信息
        LEAD(Punch_mode) OVER (PARTITION BY E_ID ORDER BY CONVERT(DATETIME, Attend_date) + CONVERT(TIME, E_Time)) AS Next_Punch_Mode,
        LEAD(Attend_date) OVER (PARTITION BY E_ID ORDER BY CONVERT(DATETIME, Attend_date) + CONVERT(TIME, E_Time)) AS Next_Attend_Date,
        LEAD(E_Time) OVER (PARTITION BY E_ID ORDER BY CONVERT(DATETIME, Attend_date) + CONVERT(TIME, E_Time)) AS Next_E_Time
    FROM ATT
)
SELECT
    E_ID,
    Attend_date AS 签到日期,
    E_Time AS 签到时间,
    Next_Attend_Date AS 签退日期,
    Next_E_Time AS 签退时间
FROM PunchRecords
WHERE Punch_mode = 1
  AND Next_Punch_Mode = 2 -- 确保下一条打卡是签退
ORDER BY E_ID, CONVERT(DATETIME, Attend_date) + CONVERT(TIME, E_Time)

关键说明： #

• PARTITION BY E_ID确保只在同一员工的打卡记录中排序
• ORDER BY使用合并后的datetime，保证跨天记录的顺序正确
• LEAD函数直接获取下一条打卡的类型和时间，筛选出签到后跟签退的有效配对

注意事项 #

• 如果你的Attend_date是DATE类型、E_Time是TIME类型，上述合并datetime的写法完全适用；如果是其他类型，可调整转换方式
• 若存在连续签到/签退的无效打卡，可根据业务规则添加额外过滤条件（比如忽略连续签到）

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