MySQL查询：单ID仅含单次进出记录时补全对应空字段

算力阿峰

2026-7-19

解决MySQL工时计算中单一进出记录的字段空值需求

原查询的问题在于直接用MIN()和MAX()取日期，无论记录是IN还是OUT状态，都会返回唯一的时间值，导致即使只有单条记录，tap_in和tap_out会重复该时间，工时计算为0，不符合预期。

你可以通过条件聚合分别提取IN/OUT状态对应的时间，并仅当两者都存在时计算工时，否则返回空值：

SELECT 
    id,
    name AS Name,
    MAX(CASE WHEN status = 'IN' THEN received_date END) AS tap_in,
    MAX(CASE WHEN status = 'OUT' THEN received_date END) AS tap_out,
    CASE 
        WHEN MAX(CASE WHEN status = 'IN' THEN received_date END) IS NOT NULL 
             AND MAX(CASE WHEN status = 'OUT' THEN received_date END) IS NOT NULL
        THEN TIMESTAMPDIFF(HOUR, 
                          MAX(CASE WHEN status = 'IN' THEN received_date END), 
                          MAX(CASE WHEN status = 'OUT' THEN received_date END))
        ELSE NULL 
    END AS work_hours
FROM tb_gate_log 
WHERE DATE(received_date) = '2023-06-06' 
GROUP BY global_id, id, name;

代码说明：

MAX(CASE WHEN status = 'IN' THEN received_date END)：仅筛选状态为IN的记录，取对应的时间（如果当天有多个IN记录，取最晚的，也可以用MIN()取最早的，根据业务需求调整），无IN记录时返回NULL。
MAX(CASE WHEN status = 'OUT' THEN received_date END)：同理提取OUT状态的时间，无OUT记录时返回NULL。
CASE判断：只有当tap_in和tap_out都不为空时，才计算工时，否则返回NULL。
注意GROUP BY需要包含id和name，避免因MySQL sql_mode限制导致的语法错误（如果你的sql_mode没有ONLY_FULL_GROUP_BY可以省略，但建议规范写法）。