SQL多表关联查询获取商品最新价格 缺失值返回NULL问题求解

问题原因 #

• 连接类型错误：当前使用的INNER JOIN仅会保留两侧表都存在匹配的行，只要companydata或pricingdata无对应价格记录，整行商品数据都会被过滤，自然无法返回NULL值，需要替换为LEFT JOIN。
• 分组逻辑错误：你将b.price1、e.price2放入了GROUP BY字段中，这会导致每个不同的价格都单独生成分组，MAX(b.date)仅能返回该价格对应的最大日期，无法实现「取最新日期对应的价格」的需求。

解决方法 #

先通过窗口函数分别提取两张价格表中每个维度的最新价格记录，再和订单主表关联即可，单条SQL即可完成需求，不需要临时表、循环或者游标：

WITH latest_company_price AS (
    SELECT * FROM (
        SELECT 
            itemid,
            companyid,
            price1,
            date,
            -- 按商品+公司分组，日期倒序取第一条即为对应维度最新价格，可根据实际业务调整分区字段
            ROW_NUMBER() OVER (PARTITION BY itemid, companyid ORDER BY date DESC) AS rn
        FROM companydata
    ) t WHERE rn = 1
),
latest_pricing_data AS (
    SELECT * FROM (
        SELECT 
            sku,
            supplierid,
            price2,
            date,
            -- 按SKU+供应商分组取最新价格，可根据实际业务调整分区字段
            ROW_NUMBER() OVER (PARTITION BY sku, supplierid ORDER BY date DESC) AS rn
        FROM pricingdata
    ) t WHERE rn = 1
)
SELECT 
    a.orderid,
    a.itemid,
    d.currency,
    d.itemcategory,
    a.originalprice,
    c.companyid,
    b.price1, -- 无匹配时自动返回NULL
    e.price2, -- 无匹配时自动返回NULL
    c.companycategory
FROM orders AS a
INNER JOIN companycodes AS c ON a.orderid = c.orderid
INNER JOIN orderoverview AS d ON a.orderid = d.orderid
-- 用LEFT JOIN关联最新价格表，无匹配返回NULL
LEFT JOIN latest_company_price AS b ON a.itemid = b.itemid AND c.companyid = b.companyid
LEFT JOIN latest_pricing_data AS e ON a.sku = e.sku
WHERE a.orderid = '123456'

如果你的数据库版本不支持窗口函数（比如MySQL 5.x及更早版本），可以用子查询先取最大日期再关联：

SELECT 
    a.orderid,
    a.itemid,
    d.currency,
    d.itemcategory,
    a.originalprice,
    c.companyid,
    b.price1,
    e.price2,
    c.companycategory
FROM orders AS a
INNER JOIN companycodes AS c ON a.orderid = c.orderid
INNER JOIN orderoverview AS d ON a.orderid = d.orderid
LEFT JOIN companydata AS b 
    ON a.itemid = b.itemid
    AND c.companyid = b.companyid
    AND b.date = (SELECT MAX(date) FROM companydata WHERE itemid = a.itemid AND companyid = c.companyid)
LEFT JOIN pricingdata AS e
    ON a.sku = e.sku
    AND e.date = (SELECT MAX(date) FROM pricingdata WHERE sku = a.sku)
WHERE a.orderid = '123456'

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