如何基于product匹配及最近更早日期Left Join table_A与table_B？

你的问题出在子查询没有限定匹配的product，导致取到的是全表范围内符合日期条件的最大日期，而非对应product的最大日期，自然关联不上，返回null。

修正方案1：给子查询添加product匹配条件 #

直接修改原SQL，在子查询里加上product = a.product，确保取到的是当前product下的最近更早日期：

SELECT a.*, b.* 
FROM table_A a
LEFT JOIN table_B b 
  ON a.product = b.product 
  AND b.date = (SELECT MAX(date) FROM table_B WHERE date <= a.date AND product = a.product)

修正方案2：使用LATERAL/APPLY关联子查询（推荐） #

这种写法更直观，直接为table_A的每条记录筛选出对应product下日期最近的table_B记录：

• 适用于PostgreSQL、MySQL 8.0+：

SELECT a.*, b.*
FROM table_A a
LEFT JOIN LATERAL (
  SELECT *
  FROM table_B b
  WHERE b.product = a.product AND b.date <= a.date
  ORDER BY b.date DESC
  LIMIT 1
) AS b ON true

• 适用于SQL Server：

SELECT a.*, b.*
FROM table_A a
OUTER APPLY (
  SELECT TOP 1 *
  FROM table_B b
  WHERE b.product = a.product AND b.date <= a.date
  ORDER BY b.date DESC
) AS b

修正方案3：先预聚合table_B的最大日期 #

先按product分组，计算每个product在各个日期点的最大可用日期，再关联table_A：

WITH b_max_dates AS (
  SELECT 
    product,
    date,
    MAX(date) OVER (PARTITION BY product ORDER BY date) AS latest_date
  FROM table_B
)
SELECT a.*, b.*
FROM table_A a
LEFT JOIN b_max_dates b
  ON a.product = b.product 
  AND b.latest_date <= a.date

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