如何在SQL关联表查询中仅获取每个商品对应指定区域的最新价格记录

原SQL问题分析 #

• 子查询关联逻辑错误：where p.id = price.id是按price表主键逐行匹配，相当于没有做同商品下最新日期的筛选，所以会返回所有匹配的价格行。
• 字段名不匹配：样例中price表的日期字段为date，原SQL使用了不存在的created_at字段，实际运行会直接报错。
• LEFT JOIN逻辑失效：WHERE条件中写p.region = 'USA'会过滤掉没有匹配到价格的商品，LEFT JOIN实际等价于INNER JOIN。

正确实现方案 #

方案1：窗口函数实现（推荐，支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

逻辑是先对同商品同区域的价格按日期倒序排序，取每组第一条就是最新价格：

SELECT i.id, i.name, p.region, p.price, p.date
FROM item i
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY date DESC) AS rn
    FROM price
    WHERE region = 'USA'
) p ON i.id = p.item_id AND p.rn = 1;

方案2：低版本MySQL兼容实现 #

通过自关联排除非最新的价格记录：

SELECT i.id, i.name, p.region, p.price, p.date
FROM item i
LEFT JOIN price p ON i.id = p.item_id
LEFT JOIN price p2 
    ON p.item_id = p2.item_id 
    AND p.region = p2.region 
    AND p.date < p2.date
WHERE p.region = 'USA' AND p2.id IS NULL;

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