SQL 在相等或最接近的日期加入

Question

我有两张 table

表 a(每个 id 一行)

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

表 b(每个 id 多行，但每个 id/date 组合都是唯一的)

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 上加入这些表，而不是在日期上加入与观察日期同一天)

即输出应该是:

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

Answer 1

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

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;

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

请注意，仅获取前 1 行 是 ANSI SQL。您的数据库可能为此目的使用其他东西，例如 TOP 或 LIMIT。