MySQL按日期过滤考勤记录：有历史记录的员工无法显示Null值的小bug

之前的问题已得到解答，但后续发现一个小问题：按日期过滤员工列表时，希望指定日期无考勤记录的员工，其考勤字段显示Null值。当前代码仅能让从未有过考勤记录的员工正常显示Null，但若员工有其他日期的考勤记录、仅指定日期无记录时，无法达到预期效果。

数据表结构 #

employee表 #

| emp_id | lastname | firstname |
+-------+-----------+-----------+
|  sgi01 | Doe      | John      |
|  sgi02 | Doe      | Karen     |  
|  sgi03 | Doe      | Vincent   |
|  sgi04 | Doe      | Kyle      | 
+--------+-------+--------------+

time_records表 #

+--------+----------+---------+----------+

当前查询结果 #

| emp_id | lastname | firstname | time_in|time_out|Ctime_in   |Ctime_out  |number_of_hrs
+-------+-----------+-----------+--------+--------+-----------+-----------+-------------+
|  sgi01 | Doe      | John      |07:00:00|16:03:22|07:00:00 AM|04:03:22 PM|09:03:22
|  sgi02 | Doe      | Karen     |07:00:00|16:03:22|07:00:00 AM|04:03:22 PM|09:03:22    
|  sgi04 | Doe      | Kyle      |Null    |Null    |Null       |Null       |0
+-------+-----------+-----------------------------+--------------------------------------+

期望查询结果 #

| emp_id | lastname | firstname | time_in|time_out|Ctime_in   |Ctime_out  |number_of_hrs
+-------+-----------+-----------+--------+--------+-----------+-----------+-------------+
|  sgi01 | Doe      | John      |07:00:00|16:03:22|07:00:00 AM|04:03:22 PM|09:03:22
|  sgi02 | Doe      | Karen     |07:00:00|16:03:22|07:00:00 AM|04:03:22 PM|09:03:22    
|  sgi03 | Doe      | Vincent   |Null    |Null    |Null       |Null       |0
|  sgi04 | Doe      | Kyle      |Null    |Null    |Null       |Null       |0
+-------+-----------+-----------------------------+--------------------------------------+

当前使用的MySQL代码 #

SELECT e.emp_id, e.lastname, e.firstname, t.time_in, t.time_out ,
TIME_FORMAT(time_in, "%r") as Ctime_in,
TIME_FORMAT(time_out, "%r") as Ctime_out,
TIMEDIFF(time_out,time_in) as number_of_hrs
FROM `employees` as e LEFT JOIN time_records as t  on e.emp_id = t.emp_id
WHERE t.date = '2024-03-02' OR t.date IS null
GROUP by e.emp_id;

问题原因与解决方案 #

问题原因 #

把日期过滤条件放在WHERE子句中，会在LEFT JOIN执行后再过滤行。对于有其他日期考勤记录的员工（比如sgi03），t.date既不等于指定日期，也不是Null，因此会被过滤掉，无法出现在结果中。

修正后的代码 #

将日期过滤条件移到LEFT JOIN的ON子句中，这样在关联阶段就只匹配指定日期的考勤记录，没有匹配的员工会保留基础信息，考勤字段自动为Null：

SELECT e.emp_id, e.lastname, e.firstname, 
       t.time_in, t.time_out,
       TIME_FORMAT(t.time_in, "%r") as Ctime_in,
       TIME_FORMAT(t.time_out, "%r") as Ctime_out,
       IFNULL(TIMEDIFF(t.time_out, t.time_in), '00:00:00') as number_of_hrs
FROM `employees` as e 
LEFT JOIN time_records as t  
  ON e.emp_id = t.emp_id AND t.date = '2024-03-02';

说明 #

1. 把t.date = '2024-03-02'加入ON条件，确保只关联指定日期的考勤记录，所有员工都会出现在结果中；
2. 用IFNULL函数处理number_of_hrs，当考勤记录不存在时显示00:00:00（若需要显示0，可替换为IFNULL(TIMEDIFF(t.time_out, t.time_in), 0)）；
3. 如果每个员工在指定日期最多只有一条考勤记录，GROUP BY可以省略；若存在多条记录，可根据需求添加聚合逻辑（比如MAX(t.time_in)）。

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