获取产品最新有效价格：能否替换查询中的嵌套子查询？

一、原查询的子查询是否合规可行？ #

语法上这个子查询是合规的，但存在逻辑错误：子查询里没限定product_id = product.id，会返回所有产品中满足is_active = true且valid_from <= now()的最新valid_from，而非针对ID为1的产品的对应日期，导致关联后拿到的可能不是该产品的正确价格。

如果给子查询补上product_id = product.id的关联条件，那它就是逻辑正确且合规可行的相关子查询，能得到预期结果。修正后的查询如下：

SELECT *
FROM product 
LEFT JOIN product_price productPrice
          ON product.id = productPrice.product_id
          AND productPrice.valid_from =
               (SELECT valid_from FROM product_price
                WHERE product_price.product_id = product.id -- 新增产品关联条件
                  AND product_price.is_active = true 
                  AND valid_from <= now()
                ORDER BY valid_from DESC LIMIT 1)
WHERE product.id = 1;

二、替代嵌套子查询的方案 #

1. 窗口函数法（推荐，兼容性好） #

用ROW_NUMBER()按产品分组，直接筛选出每个产品的最新有效价格，逻辑清晰且性能优化更友好：

SELECT p.*, pp.*
FROM product p
LEFT JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY product_id 
                              ORDER BY valid_from DESC) AS rn
    FROM product_price
    WHERE is_active = true 
      AND valid_from <= now()
) pp ON p.id = pp.product_id AND pp.rn = 1
WHERE p.id = 1;

2. 聚合+关联法 #

先统计每个产品的最新有效valid_from，再关联价格表获取完整数据，适合不支持窗口函数的老版本数据库：

SELECT p.*, pp.*
FROM product p
LEFT JOIN (
    SELECT product_id, MAX(valid_from) AS latest_valid_from
    FROM product_price
    WHERE is_active = true 
      AND valid_from <= now()
    GROUP BY product_id
) latest_pp ON p.id = latest_pp.product_id
LEFT JOIN product_price pp ON latest_pp.product_id = pp.product_id 
                          AND latest_pp.latest_valid_from = pp.valid_from
WHERE p.id = 1;

3. 横向关联法（PostgreSQL、MySQL 8.0+支持） #

通过LATERAL JOIN为目标产品直接匹配最新价格记录，可读性和性能表现都不错：

SELECT p.*, pp.*
FROM product p
LEFT JOIN LATERAL (
    SELECT *
    FROM product_price
    WHERE product_id = p.id
      AND is_active = true 
      AND valid_from <= now()
    ORDER BY valid_from DESC
    LIMIT 1
) pp ON true
WHERE p.id = 1;

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