如何将Attendance表分组查询员工ClockIn/ClockOut考勤记录

问题场景 #

现有Attendance表结构及数据如下：

需要将数据转换为以下格式，把同一员工同一天的上下班时间合并到一行：

其中ClockIn对应AttType='IN'的时间，ClockOut对应AttType='OUT'的时间。

原查询的问题 #

你尝试的SQL语句仅使用GROUP BY但未对ClockIn和ClockOut字段使用聚合函数，导致每条IN/OUT记录仍单独显示，无法合并：

SELECT EmpID,
       DateEntry,
       CASE
           WHEN AttType = 'IN' THEN DateEntry
           ELSE NULL
       END AS ClockIn,
       CASE
           WHEN AttType = 'OUT' THEN DateEntry
           ELSE NULL
       END AS ClockOut
FROM Attendance
GROUP BY EmpID,
         DateEntry; 

正确解决方案 #

方法1：聚合函数+CASE表达式（推荐） #

通过MAX()或MIN()聚合函数配合CASE表达式，将同一分组内的非NULL值提取出来，实现合并：

SELECT 
    CAST(DateEntry AS DATE) AS DateEntry,
    EmpID,
    MAX(CASE WHEN AttType = 'IN' THEN TIME(DateEntry) END) AS ClockIn,
    MAX(CASE WHEN AttType = 'OUT' THEN TIME(DateEntry) END) AS ClockOut
FROM Attendance
GROUP BY EmpID, CAST(DateEntry AS DATE);

关键说明：

• CAST(DateEntry AS DATE)：提取日期部分，确保同一员工同一天的记录被分到同一组
• MAX(CASE ...)：由于每个员工每天只有一条IN和一条OUT记录，MAX()会自动取出该分组内非NULL的时间值，从而将IN/OUT合并到一行
• 如果存在员工某天只有IN或只有OUT的情况，对应的字段会显示NULL，不会丢失记录

方法2：自连接 #

通过将表自身连接，匹配同员工同一天的IN和OUT记录：

SELECT 
    CAST(a_in.DateEntry AS DATE) AS DateEntry,
    a_in.EmpID,
    TIME(a_in.DateEntry) AS ClockIn,
    TIME(a_out.DateEntry) AS ClockOut
FROM Attendance a_in
INNER JOIN Attendance a_out 
    ON a_in.EmpID = a_out.EmpID 
    AND CAST(a_in.DateEntry AS DATE) = CAST(a_out.DateEntry AS DATE)
    AND a_in.AttType = 'IN' 
    AND a_out.AttType = 'OUT';

关键说明：

• 仅会返回同时存在IN和OUT记录的员工日期组合，若某天只有IN或只有OUT，该记录会被过滤
• 如果数据库支持DATE()函数（如MySQL），可以用DATE(DateEntry)替代CAST(DateEntry AS DATE)简化写法

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