mysql - 如何在 mysql 查询中用 null 替换丢失的记录？

Question

我得到一个单位进行的测试数量:

  select 
    date(START_DATE_TIME), product_id, BATCH_SERIAL_NUMBER, count(*) 
from 
    ( select START_DATE_TIME, product_id, uut_serial_number, BATCH_SERIAL_NUMBER 
      from uut_result    
      where START_DATE_TIME >= '2016-07-01 00:00:00' 
      and START_DATE_TIME <= '2016-07-07 23:59:59') as passtbl
      group by date(START_DATE_TIME), product_id, batch_serial_number;

我获取按天分割的单元通过的测试数量:

  select 
    date(START_DATE_TIME), product_id, BATCH_SERIAL_NUMBER, count(*) 
from 
    ( select START_DATE_TIME, product_id, uut_serial_number, BATCH_SERIAL_NUMBER 
      from uut_result    
      where START_DATE_TIME >= '2016-07-01 00:00:00' 
      and START_DATE_TIME <= '2016-07-07 23:59:59'     
      and uut_status = 'passed' ) as passtbl
      group by date(START_DATE_TIME), product_id, batch_serial_number;

我发现有些单元根本没有任何通过记录，因此第二个查询返回的记录比第一个查询少。这破坏了后期处理。有没有办法捕获记录的缺失并将其替换为 null 或其他虚拟值？

Answer 1

select date(START_DATE_TIME), 
       product_id, 
       BATCH_SERIAL_NUMBER, 
       status, 
       count(*) 
from (select *, 
          case when uut_status = 'passed' then uut_status 
               else 'other statuses'
               end status
      from uut_result)    
where START_DATE_TIME >= '2016-07-01 00:00:00' 
and START_DATE_TIME <= '2016-07-07 23:59:59'
group by date(START_DATE_TIME), 
         status, 
         product_id, 
         batch_serial_number;