SQL Server如何按双条件拆分同列并聚合员工打卡考勤数据

最初查询的核心缺陷：子查询未和外层行建立关联匹配逻辑，相当于全表范围内取所有state=0、state=1的时间值，既没有按员工编码关联，也没有按打卡日期做维度匹配，因此会出现大量错位、NULL值，无法把同一员工同一天的签到、签退归为同一行结果。
之前尝试窗口函数未生效，大概率是没有按「员工+打卡日期」做分区，直接对全表排序导致跨员工、跨天取到无关的下一条记录。

方案1：条件聚合（通用适配全SQL引擎） #

利用“同一员工同一天仅存在签到、签退两条记录”的规则，按员工+打卡自然日期分组，用条件判断把同组内的两类打卡时间拆成独立列，再计算时长。

SELECT
    employee_code,
    -- 提取组内签到时间
    MIN(CASE WHEN status = 'check_in' THEN created END) AS check_in_at,
    -- 提取组内签退时间
    MAX(CASE WHEN status = 'check_out' THEN created END) AS check_out_at,
    -- 计算在岗时长，格式化为HH:MM:SS
    CONVERT(
        TIME,
        MAX(CASE WHEN status = 'check_out' THEN created END) 
        - MIN(CASE WHEN status = 'check_in' THEN created END)
    ) AS total_time
FROM attendance_record
-- 核心：按员工、打卡日期分组，保证配对维度正确
GROUP BY employee_code, CAST(created AS DATE)
ORDER BY check_in_at DESC;

方案2：LEAD窗口函数实现（匹配你之前的窗口函数尝试思路） #

正确逻辑是在每个员工每天的打卡序列里，签到行的下一条记录就是当日签退，直接取值即可：

WITH ordered_attendance AS (
    SELECT
        employee_code,
        created,
        status,
        -- 按员工、当天日期分区，时间升序，取当前行的下一条打卡时间
        LEAD(created, 1) OVER (
            PARTITION BY employee_code, CAST(created AS DATE)
            ORDER BY created
        ) AS paired_clock_time
    FROM attendance_record
)
SELECT
    employee_code,
    created AS check_in_at,
    paired_clock_time AS check_out_at,
    CONVERT(TIME, paired_clock_time - created) AS total_time
FROM ordered_attendance
-- 只保留签到行，跳过后面的签退行，每个员工每天仅出1条结果
WHERE status = 'check_in'
ORDER BY check_in_at DESC;

• 时间差函数根据所用SQL引擎微调即可：MySQL环境下可替换为SEC_TO_TIME(TIMESTAMPDIFF(SECOND, check_in_at, check_out_at))生成HH:MM:SS格式的时长值；PostgreSQL直接用两个datetime相减即可得到标准间隔格式
• 如果存在单日多次打卡、补卡的场景，可调整窗口函数的排序逻辑，把连续两次打卡作为配对单元，不需要强依赖固定的check_in/check_out状态值。

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