代码之家  ›  专栏  ›  技术社区  ›  Raymart Calinao

如果列表中没有记录,则强制显示null值

  •  0
  • Raymart Calinao  · 技术社区  · 1 年前

    我想问一个关于左联接和分组方式的问题,因为文档中的示例更多的是基本示例,我正在寻找一个带有where子句的组合分组方式,它符合我希望显示的需求

    我的桌子是

    员工表

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

    时间记录表

    | emp_id | date     |time_in  |time_out  |      
    +--------+----------+---------|----------|
    |  sgi01 |2024-03-01|07:00:00 |16:03:22  | 
    |  sgi02 |2024-03-01|07:00:00 |16:03:22  |  
    +--------+----------+---------+----------+
    

    结果表

    | 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    
    +-------+-------+---------------+-------------------------------------------------------+
    

    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-01'
    GROUP by e.emp_id;
    

    想要或想要这样显示

    | 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 or 0|Null or 0|Null or 0  |Null or 0  |Null or 0|
    |  sgi04 | Doe      | Kyle      |Null or 0|Null or 0|Null or 0  |Null or 0  |Null or 0|
    +-------+-------+---------------+-------------------------------------------------------+
    

    通常,我只会使用group by和left join来强制将另一条记录视为null,但当我尝试包括日期范围时,它只会给我有数据的记录。。

    1 回复  |  直到 1 年前
        1
  •  2
  •   Christian    1 年前

    问题是其他员工在time_records中没有记录,但您的情况是这样的:

    where t.date = '2024-03-01'
    

    要将它们包括在time_records中(即使没有记录),请将其更改为以下内容:

    where t.date = '2024-03-01' OR t.date IS NULL