如何用高效SQL转置TCPunch表打卡记录为ClockIn/ClockOut列并计算时长？

下面的SQL语句可实现打卡记录配对、转置及工作时长计算，同时保留缺失打卡对应的NULL值：

WITH ClockInRecords AS (
    SELECT 
        EmployeeID,
        ActionTime AS ClockIn,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ActionTime) AS RowNum
    FROM TCPunch
    WHERE ActionID = 1
),
ClockOutRecords AS (
    SELECT 
        EmployeeID,
        ActionTime AS ClockOut,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ActionTime) AS RowNum
    FROM TCPunch
    WHERE ActionID = 2
)
SELECT 
    COALESCE(cin.EmployeeID, cout.EmployeeID) AS EmployeeID,
    cin.ClockIn,
    cout.ClockOut,
    CASE 
        WHEN cin.ClockIn IS NOT NULL AND cout.ClockOut IS NOT NULL 
        THEN DATEDIFF(second, cin.ClockIn, cout.ClockOut)/3600.0 
        ELSE NULL 
    END AS Hours
FROM ClockInRecords cin
FULL JOIN ClockOutRecords cout 
    ON cin.EmployeeID = cout.EmployeeID 
    AND cin.RowNum = cout.RowNum
ORDER BY EmployeeID, COALESCE(cin.ClockIn, cout.ClockOut);

逻辑说明 #

1. 拆分打卡记录：

   • ClockInRecords筛选所有上班打卡（ActionID=1），按员工分组、打卡时间排序，给每条记录分配序号RowNum标记为该员工的第n次上班。
   • ClockOutRecords同理处理下班打卡（ActionID=2），标记为该员工的第n次下班。

2. 配对打卡记录：

   • 使用FULL JOIN基于员工ID和序号RowNum合并两个数据集，确保单独的上班/下班记录不被丢弃，缺失的对应列自动显示NULL。

3. 计算工作时长：

   • 仅当同时存在上班和下班时间时，用DATEDIFF计算秒数并转换为小时数，否则时长列显示NULL。

4. 排序输出：

   • 按员工ID和打卡时间（优先用上班时间，无则用下班时间）排序，与需求一致。

验证示例数据 #

执行上述SQL后，针对你提供的示例数据，会得到完全符合要求的输出：

性能优化建议 #

如果TCPunch表数据量较大，建议创建复合索引提升查询效率：

CREATE INDEX IX_TCPunch_EmployeeID_ActionID_ActionTime ON TCPunch(EmployeeID, ActionID, ActionTime);

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