MySQL多表关联查询：获取所有产品对应最新价格的最优方案咨询

嘿，你的需求很常见——要拉取所有产品，同时关联它们最新的价格记录（按prices表的id最大判定最新），你的现有写法逻辑是对的，但确实有更高效、可读性更好的替代方案，我给你分享两种常用的优化思路：

方案一：使用窗口函数（MySQL 8.0+推荐） #

如果你的MySQL版本是8.0及以上，窗口函数是最简洁且性能最优的选择，它能直接对每个产品的价格记录分组排序，筛选出最新的那一条：

SELECT 
    p.id, 
    p.name, 
    p.cat, 
    pr.cod, 
    pr.price
FROM products p
LEFT JOIN (
    SELECT 
        prod_id, 
        cod, 
        price,
        -- 按产品分组，价格记录按id倒序排，标记每条记录的排名
        ROW_NUMBER() OVER (PARTITION BY prod_id ORDER BY id DESC) AS rn
    FROM prices
) pr ON p.id = pr.prod_id AND pr.rn = 1;

逻辑说明： #

1. 子查询里用ROW_NUMBER()给每个prod_id分组的价格记录按id降序编号，排名为1的就是该产品的最新价格记录
2. 用左连接关联products表，确保没有价格记录的产品（比如prod2）也能被保留，对应的cod和price会显示为null

方案二：分组取最大ID再关联（兼容MySQL 5.x） #

如果你的MySQL版本还停留在5.x（不支持窗口函数），可以先分组找出每个产品对应的最大价格ID，再关联prices表获取具体字段，这种写法比你的原关联子查询性能更优（分组只执行一次，而非每行都执行子查询）：

SELECT 
    p.id, 
    p.name, 
    p.cat, 
    pr.cod, 
    pr.price
FROM products p
LEFT JOIN (
    -- 先找出每个产品的最新价格记录ID
    SELECT prod_id, MAX(id) AS max_price_id
    FROM prices
    GROUP BY prod_id
) max_pr ON p.id = max_pr.prod_id
-- 再关联prices表拿到对应的cod和price
LEFT JOIN prices pr ON max_pr.max_price_id = pr.id;

对比原方案 #

你的原查询（关联子查询）逻辑正确，但在数据量较大时，由于每条产品记录都要执行一次子查询，性能会比上面两种方案差。而窗口函数方案不仅代码更简洁，MySQL对其的查询优化也更到位；分组关联方案则兼顾了老版本兼容性和性能。

这两种方案都能完美输出你预期的结果，包括prod2的cod和price为null的情况。

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