如何在SQL表连接时用最近日期的非空值填充空值

表结构与测试数据 #

CREATE TABLE a
( a_value int,
  a_date DATE,
  a_id int
);

CREATE TABLE b
( b_id int,
  b_date DATE,
  b_value float
);

INSERT INTO a VALUES (1, '20130603', 1);
INSERT INTO a VALUES (1, '20130704', 1);
INSERT INTO a VALUES (3, '20130805', 1);
INSERT INTO a VALUES (1, '20130906', 1);
INSERT INTO a VALUES (2, '20131007', 1);

INSERT INTO b VALUES (1, '20130603', 3.00);
INSERT INTO b VALUES (1, '20130706', 4.00);
INSERT INTO b VALUES (1, '20130906', 5.00);

原查询及问题 #

执行以下右连接查询后，部分a_date对应的b_value和b_date字段出现空值：

SELECT a.a_value,
a.a_date,
a.a_id,
b.b_value,
b.b_date
FROM b
RIGHT JOIN a on a.a_id = b.b_id
AND b.b_date <= a.a_date
AND b.b_date >= timestampadd(day, -25, a.a_date)
ORDER BY a.a_date;

原查询结果 #

期望结果 #

希望用最近日期的非空b表值填充空值，得到如下结果：

尝试方案及问题 #

尝试用coalesce子查询补充空值，但出现重复行：

and coalesce(b.b_date, 
             (select b_date 
              from b as b1 
              where b1.b_date <a.a_date and b1.b_value is not null
              order by b1.b_date desc
             LIMIT 1)) <= a.a_date;

解决方案 #

原连接逻辑无法精准匹配每个a记录对应的最近b记录，且coalesce的用法未控制结果行数。以下是两种可行方案：

方案1：使用LATERAL JOIN（MySQL 8.0+/PostgreSQL等支持） #

为每个a记录单独查询符合条件的最近b记录，避免重复行：

SELECT 
    a.a_value,
    a.a_date,
    a.a_id,
    b.b_value,
    b.b_date
FROM a
LEFT JOIN LATERAL (
    -- 为当前a记录找到最近的、不晚于a_date的b记录
    SELECT b_value, b_date
    FROM b
    WHERE b.b_id = a.a_id 
      AND b.b_date <= a.a_date
    ORDER BY b.b_date DESC
    LIMIT 1
) b ON 1=1
ORDER BY a.a_date;

方案2：使用窗口函数（兼容更多数据库） #

通过窗口函数对b表数据按日期排序，再关联a表筛选最近记录：

WITH ranked_b AS (
    SELECT 
        b.*,
        -- 按b_id分组，日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY b.b_id ORDER BY b.b_date DESC) AS rn
    FROM b
)
SELECT 
    a.a_value,
    a.a_date,
    a.a_id,
    b.b_value,
    b.b_date
FROM a
LEFT JOIN ranked_b b 
    ON a.a_id = b.b_id 
    AND b.b_date <= a.a_date
-- 筛选每个a记录对应的最近b记录
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.a_id, a.a_date ORDER BY b.b_date DESC) = 1
ORDER BY a.a_date;

说明 #

如果需要保留原查询中25天以内的限制，只需在子查询的WHERE条件中添加AND b.b_date >= TIMESTAMPADD(DAY, -25, a.a_date)即可。

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