在相同或最接近的日期上进行 SQL 连接

我有两张桌子

表 a（每个 id 1 行）

id,observation_date
a,2015-03-01
b,2015-03-03
c,2015-03-05

表 b（每个 ID 有多行，但每个 ID/日期组合都是唯一的）

id, insert_date, value
a,2015-02-28,x1
a,2015-03-01,x2
a,2015-03-02,x3
b,2015-02-28,x4
b,2015-03-01,x5
b,2015-03-02,x6
c,2015-02-28,x7
c,2015-03-01,x8
c,2015-03-02,x9
c,2015-03-03,x10
c,2015-03-04,x11

我想在 id 上加入这些表，但也不想加入我想加入表 b 中与表 a 中的观察日期相关的最近插入日期的日期（其中日期是在同一天之前或同一天）作为观察日期）

即输出应该是：

id,observation_date,insert_date,value
a,2015-03-01,2015-03-01,x2
b,2015-03-03,2015-03-02,x6
c,2015-03-05,2015-03-04,x11

执行此操作的一种典型方法是使用相关子查询，每个值对应一个子查询：

select a.*,
       (select b.date
        from b
        where b.id = a.id and b.insert_date <= a.observation_date
        order by b.insert_date desc
        fetch first 1 row only
       ) as insert_date,
       (select b.value
        from b
        where b.id = a.id and b.insert_date <= a.observation_date
        order by b.insert_date desc
        fetch first 1 row only
       ) as value
from a;

也有两种类似的方法可用。使用相关子查询获取日期，然后连接回表以获取其余值。或者，如果您的数据库支持，请使用横向联接（使用apply在 SQL Server 中）。更复杂的方法涉及连接和group by，我不推荐。

注意fetch first 1 row only是 ANSI SQL。您的数据库可能会使用其他东西来达到此目的，例如TOP or LIMIT.