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

我得到一个单元进行的测试次数：

  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或其他一些哑数值？

最佳答案

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;

关于mysql - 如何在mysql查询中用空值替换丢失的记录？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38453570/