试试这个；

with inv as
(
select  m.material
       ,i.date
       ,i.final_stock
       ,Max(i.Entry_time) over (Partition By i.material) maxLog
from materials m
left join inventory i on m.material = i.material 
)
Select date, material, final_stock
From inv where entry_time = maxLog

只需添加一个子查询来填充缺少的元素，然后使用条件来填充空字段。下面的查询符合您问题中要求的条件。
请注意，在“实时”系统中，我不会仅将任何给定日期的最大值（最终库存）作为“最终库存”项。如果“entry\u time”应该是给定库存项目的最新条目，我将重新处理此查询以将其用作要引用的最新“inventory entry”。但这样做会带来一个问题，即是否允许系统中的某个人对库存条目进行回溯。

SELECT 
  ifnull(C.date,(SELECT max(date) FROM inventory)) AS date,
  C.material,
  ifnull(max(D.final_stock),0) AS final_stock 
FROM (
  SELECT
    max(B.date) AS date,
    A.material 
  FROM materials A LEFT JOIN inventory B ON A.material = B.material 
  GROUP BY A.material
  ) AS C LEFT JOIN inventory D ON C.material=D.material AND C.date = D.date 
  GROUP BY C.material,C.date
  ORDER BY C.material,C.date

结果如下

date        material        final_stock
2018-05-18  Lily - Blue     90
2018-05-18  Lily - Orange   0
2018-05-18  Lily - White    10
2018-05-18  Rose - Blue     0
2018-05-18  Rose - Orange   0
2018-05-18  Rose - White    0

如果允许您使用entry\u time字段，那么下面的查询是更好的解决方案。此查询也不再需要在外部查询中使用聚合。数据集的结果是相同的。

SELECT 
  ifnull(D.date,(SELECT max(date) FROM inventory)) AS date,
  C.material,
  ifnull(D.final_stock,0) AS final_stock 
FROM (
  SELECT
    max(B.entry_time) as entry_time,
    A.material 
  FROM materials A LEFT JOIN inventory B ON A.material = B.material 
  GROUP BY A.material
  ) AS C LEFT JOIN inventory D ON 
    C.material=D.material AND 
    C.entry_time = D.entry_time
    ORDER BY C.material

一种方法是相关子查询：

select m.*,
       coalesce( (select i.final_stock
                  from inventory i
                  where i.material = m.material
                  order by i.entry_time desc
                  limit 1
                 ), 0) as final_stock
from materials m;

请注意 id 在表中，它可以用于外键关系。因此， inventory 应该有一个 material_id 列，而不是 material 列。

你也可以做 JOIN 与 subquery :

select m.material, coalesce(i.final_stock, 0) as final_stock
from materials m 
left outer join (select material, max(final_stock) final_stock
                 from INVENTORY 
                 group by material  
                 ) i on i.material = m.material;