如何在满足条件时限制JOIN仅匹配第一条记录？

问题分析 #

原SQL存在两个核心错误：

1. 关联条件错误：误用ord.id = prd.id关联，产品价格表prd无id字段，应按产品名称关联，即ord.product = prd.product
2. 未筛选目标记录：仅对prd表排序无法实现“取符合日期条件的最新价格”，需通过窗口函数或子查询锁定对应记录

方案一：窗口函数实现（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

SELECT 
    ord.order_date, 
    ord.id, 
    ord.product, 
    prd.price
FROM ord
LEFT JOIN (
    SELECT 
        product, 
        price, 
        start_date,
        -- 按产品分组，生效日期倒序排名，最新生效记录排第1
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY start_date DESC) AS rn
    FROM prd
) AS prd 
    ON ord.product = prd.product 
    AND ord.order_date >= prd.start_date
    AND prd.rn = 1;

方案二：子查询筛选（兼容低版本数据库） #

SELECT 
    ord.order_date, 
    ord.id, 
    ord.product, 
    prd.price
FROM ord
LEFT JOIN prd
    ON ord.product = prd.product
    AND prd.start_date = (
        -- 找到当前产品下，小于等于订单日期的最大生效日期
        SELECT MAX(start_date) 
        FROM prd p 
        WHERE p.product = ord.product 
        AND p.start_date <= ord.order_date
    );

验证结果 #

执行上述任意SQL后，将得到期望结果：

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