如何关联两SQL表实现带MAX条件的全物料最新库存查询

先帮你梳理下原查询的问题：你没对物料做分组，所以只返回了一条汇总结果；同时没处理无库存记录时的NULL值，也没包含需求里的date字段。下面给你两种适配不同场景的正确查询方案：

场景1：固定查询指定日期（比如你期望输出里的2018-05-18） #

SELECT 
    '2018-05-18' AS date,
    m.material,
    COALESCE(MAX(i.final_stock), 0) AS max_final_stock
FROM 
    materials m
LEFT JOIN 
    inventory i ON m.material = i.material AND i.date = '2018-05-18'
GROUP BY 
    m.material
ORDER BY 
    m.material;

关键细节解释：

• COALESCE(MAX(i.final_stock), 0)：当物料没有对应库存记录时，MAX(i.final_stock)会返回NULL，这个函数会把空值替换成0，完美匹配需求。
• LEFT JOIN里加i.date = '2018-05-18'：确保只关联当天的库存数据，避免跨日期的无效关联。
• GROUP BY m.material：按物料分组，保证每个物料都能返回一行结果，而不是只输出一条汇总。

场景2：自动取库存表中最新日期的记录（更灵活的通用方案） #

如果你的需求不是固定某一天，而是要自动获取每个物料的最新库存记录（依据entry_time判断先后），可以用窗口函数实现：

WITH latest_inventory AS (
    SELECT 
        material,
        final_stock,
        date,
        -- 按物料分组，按入库时间倒序编号，第一条就是最新记录
        ROW_NUMBER() OVER (PARTITION BY material ORDER BY entry_time DESC) AS rn
    FROM 
        inventory
)
SELECT 
    -- 无库存记录时用默认日期，也可以换成NULL
    COALESCE(li.date, '2018-05-18') AS date,
    m.material,
    COALESCE(li.final_stock, 0) AS max_final_stock
FROM 
    materials m
LEFT JOIN 
    latest_inventory li ON m.material = li.material AND li.rn = 1
ORDER BY 
    m.material;

内容的提问来源于stack exchange，提问作者Irfan Harun