基于Datetime列聚合实现4列Pivot转换技术求助

核心思路 #

先给同一员工同一天的打卡记录按时间先后分配序号，再通过透视将序号对应的时间转成4列，实现每日一行的结构。

假设数据结构 #

假设打卡表名为employee_attendance，包含字段：

• emp_id：员工ID
• punch_datetime：打卡时间戳

方案1：SQL Server 原生PIVOT语法 #

WITH ranked_attendance AS (
    SELECT
        emp_id,
        CAST(punch_datetime AS DATE) AS punch_date,
        punch_datetime,
        -- 按员工+日期分组，给打卡时间按先后排1-4的序号
        ROW_NUMBER() OVER (PARTITION BY emp_id, CAST(punch_datetime AS DATE) ORDER BY punch_datetime) AS punch_seq
    FROM employee_attendance
)
SELECT
    emp_id,
    punch_date,
    [1] AS Morning_In,
    [2] AS Morning_Out,
    [3] AS Afternoon_In,
    [4] AS Afternoon_Out
FROM ranked_attendance
PIVOT (
    MAX(punch_datetime)
    FOR punch_seq IN ([1], [2], [3], [4])
) AS pivot_table
ORDER BY emp_id, punch_date;

方案2：MySQL/通用SQL（无原生PIVOT） #

如果数据库不支持PIVOT，可用CASE WHEN配合分组实现：

SELECT
    emp_id,
    CAST(punch_datetime AS DATE) AS punch_date,
    MAX(CASE WHEN punch_seq = 1 THEN punch_datetime END) AS Morning_In,
    MAX(CASE WHEN punch_seq = 2 THEN punch_datetime END) AS Morning_Out,
    MAX(CASE WHEN punch_seq = 3 THEN punch_datetime END) AS Afternoon_In,
    MAX(CASE WHEN punch_seq = 4 THEN punch_datetime END) AS Afternoon_Out
FROM (
    SELECT
        emp_id,
        punch_datetime,
        ROW_NUMBER() OVER (PARTITION BY emp_id, CAST(punch_datetime AS DATE) ORDER BY punch_datetime) AS punch_seq
    FROM employee_attendance
) AS ranked_attendance
GROUP BY emp_id, CAST(punch_datetime AS DATE)
ORDER BY emp_id, punch_date;

补充说明 #

• punch_seq会给每个员工每天的打卡记录按时间顺序标记1到4，若当天打卡次数超过4次会继续编号，可根据实际需求调整逻辑（比如只取前4条）
• 列名可按需修改（如Sign_In_1、Sign_Out_1等）
• 若员工当天打卡次数不足4次，对应列会显示NULL，可用COALESCE函数替换为默认值（比如COALESCE([1], '无打卡')）

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