如何通过SELECT查询将员工打卡时间戳列表转换为起止时间表？

可以实现，以下是针对该需求的SELECT查询方案，适用于大多数支持窗口函数的SQL数据库（如MySQL 8+、PostgreSQL、SQL Server等）：

原始数据结构 #

目标结果结构 #

实现SQL查询 #

WITH grouped_attendance AS (
    SELECT 
        姓名,
        员工编号,
        日期,
        打卡时间,
        -- 按员工+日期分组，将连续奇偶打卡序号归为同一时间段组
        CEIL(打卡序号 / 2) AS period_group,
        -- 标记当前打卡是时间段的开始（奇数序号）还是结束（偶数序号）
        MOD(打卡序号, 2) AS is_start
    FROM 打卡表
)
SELECT 
    ga1.姓名,
    ga1.员工编号,
    ga1.日期,
    ga1.打卡时间 AS 开始时间,
    ga2.打卡时间 AS 结束时间,
    -- 计算时长，仅当结束时间存在时生成格式为"小时:分钟"的结果
    CASE 
        WHEN ga2.打卡时间 IS NOT NULL THEN 
            TIMESTAMPDIFF(MINUTE, STR_TO_DATE(ga1.打卡时间, '%H:%i'), STR_TO_DATE(ga2.打卡时间, '%H:%i')) DIV 60 
            || ':' || 
            LPAD(TIMESTAMPDIFF(MINUTE, STR_TO_DATE(ga1.打卡时间, '%H:%i'), STR_TO_DATE(ga2.打卡时间, '%H:%i')) MOD 60, 2, '0')
        ELSE NULL 
    END AS 时长
FROM grouped_attendance ga1
LEFT JOIN grouped_attendance ga2
    ON ga1.姓名 = ga2.姓名
    AND ga1.员工编号 = ga2.员工编号
    AND ga1.日期 = ga2.日期
    AND ga1.period_group = ga2.period_group
    AND ga2.is_start = 0 -- 匹配同组的偶数序号记录作为结束时间
WHERE ga1.is_start = 1 -- 仅取奇数序号记录作为时间段起始
ORDER BY ga1.日期 DESC, ga1.姓名, ga1.period_group;

逻辑说明 #

1. 分组标记：通过CEIL(打卡序号/2)把连续的奇偶打卡序号归为同一个时间段组，比如序号1、2为组1，3、4为组2；
2. 关联匹配：用左连接将每个时间段的起始打卡（奇数序号）和结束打卡（偶数序号）配对，处理最后一条是奇数序号的情况（此时结束时间为NULL）；
3. 时长计算：将字符串格式的打卡时间转为时间类型，计算分钟差后转换为小时:分钟格式，无结束时间时显示NULL；
4. 排序整理：按日期倒序、姓名、时间段组排序，保证结果顺序与示例一致。

注意事项 #

• 不同数据库的时间函数需对应调整：比如PostgreSQL用TO_TIMESTAMP，SQL Server用DATEDIFF和FORMAT；
• 需确保打卡序号在每个员工+日期的分组内是连续递增的，否则分组逻辑会出错。

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