SQL关联查询：匹配最近的相同或更早日期

场景说明 #

现有两张表：

• T_VEN：存储多款产品的销售日期
• T_COM：存储这些产品的每日成本

需求：关联两张表，获取产品销售当日的成本；若当日无对应成本数据，则取最近前一天的成本，逻辑等同于Excel中XLOOKUP函数设置Match_mode=-1（精确匹配或返回下一个更小值）。

示例数据 #

T_VEN表（销售数据） #

T_COM表（成本数据） #

期望结果 #

解决方案 #

核心逻辑：对每个销售记录，匹配同产品下日期小于等于销售日期的最大成本日期，取对应成本值。以下是不同数据库的实现方式：

1. 通用子查询方式（适用于多数数据库） #

SELECT 
    v.PROD,
    v.DATE,
    (SELECT c.COST
     FROM T_COM c
     WHERE c.PROD = v.PROD
       AND c.DATE <= v.DATE
     ORDER BY c.DATE DESC
     LIMIT 1) AS COST
FROM T_VEN v;

2. 窗口函数方式（支持ROW_NUMBER()的数据库，如MySQL 8+/PostgreSQL/SQL Server） #

SELECT 
    PROD,
    DATE,
    COST
FROM (
    SELECT 
        v.PROD,
        v.DATE,
        c.COST,
        ROW_NUMBER() OVER (PARTITION BY v.PROD, v.DATE ORDER BY c.DATE DESC) AS rn
    FROM T_VEN v
    LEFT JOIN T_COM c 
        ON c.PROD = v.PROD
        AND c.DATE <= v.DATE
) t
WHERE rn = 1;

3. LATERAL JOIN/APPLY方式（PostgreSQL/SQL Server） #

PostgreSQL：

SELECT 
    v.PROD,
    v.DATE,
    c.COST
FROM T_VEN v
LEFT JOIN LATERAL (
    SELECT COST
    FROM T_COM c
    WHERE c.PROD = v.PROD
      AND c.DATE <= v.DATE
    ORDER BY c.DATE DESC
    LIMIT 1
) c ON true;

SQL Server：

SELECT 
    v.PROD,
    v.DATE,
    c.COST
FROM T_VEN v
OUTER APPLY (
    SELECT TOP 1 COST
    FROM T_COM c
    WHERE c.PROD = v.PROD
      AND c.DATE <= v.DATE
    ORDER BY c.DATE DESC
) c;

注意事项 #

• 确保DATE字段为日期类型，而非字符串；若为字符串需先转换（如MySQL中用STR_TO_DATE(v.DATE, '%d/%m/%Y')）
• 若某产品无任何历史成本数据，结果中COST会返回NULL，可根据需求用COALESCE设置默认值

内容的提问来源于stack exchange，提问作者Marcos Paulo Ferreira Gonalves