mysql - 将两个具有多个条件和一个最大条件的 sql 表组合在一起的最佳方法

Question

我有两个表: Material 和库存。

我想获取所有 Material 的列表及其最新库存(如果有)，否则仅为零。

表格 - Material

id        | material    | 
==========================
1         |Lily - Blue    
2         |Lily - Orange    
3         |Lily - White
4         |Rose - Blue    
5         |Rose - Orange    
6         |Rose - White

表格 - 库存

id        | date      | material   | final_stock | entry_time           | 
=========================================================================
1         |2018-05-18 |Lily - Blue |  30         |  2018-05-18 09:06:48 |        
2         |2018-05-18 |Lily - white|  10         |  2018-05-18 10:32:27 |        
3         |2018-05-18 |Lily - Blue |  90         |  2018-05-18 15:30:31 |   

我的查询:

select materials.material, max(inventory.final_stock) from materials left join 
inventory on materials.material = inventory.material

输出

material    | max(inventory.final_stock)| 
========================================
Lily - Blue |90

需要输出>>

date       | material     | max(inventory.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    

Answer 1

一种方法是相关子查询:

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 列。