sql - 如果表A的值在表B中不可用，则从表B中选择先前的可用值

我想显示表A中与表B中日期匹配的所有日期。
如果表A的日期在表B中不可用，则应选择以前可用的日期。
例如，
我有A桌

Date1
"2017-04-10"
"2017-04-11"
"2017-04-12"
"2017-04-13"
"2017-04-14"
"2017-04-15"
"2017-04-16"
"2017-04-17"
"2017-04-18"
"2017-04-19"
"2017-04-20"

表B为

Date2
"2017-04-10"
"2017-04-11"
"2017-04-12"
"2017-04-13"
"2017-04-18"
"2017-04-19"
"2017-04-20"

我看到的结果是，

Date1        | NewDate
"2017-04-10" | "2017-04-10"
"2017-04-11" | "2017-04-11"
"2017-04-12" | "2017-04-12"
"2017-04-13" | "2017-04-13"
"2017-04-14" | "2017-04-13"
"2017-04-15" | "2017-04-13"
"2017-04-16" | "2017-04-13"
"2017-04-17" | "2017-04-13"
"2017-04-18" | "2017-04-18"
"2017-04-19" | "2017-04-19"
"2017-04-20" | "2017-04-20"

有人能帮我一下吗？

最佳答案

一种方法使用横向联接或相关子查询：

select a.*,
       (select b.date
        from b
        where b.date <= a.date
        order by b.date desc
        limit 1
       ) b
from a;

如果您有大量数据，那么以下方法可能更有效：

select a.date, b_date
from (select a.date,
             max(b_date) over (order by date, b_date) as b_date
      from ((select a.date as date, null as b_date
             from a
            ) union all
            (select b.date as date, b.date as b_date
             from b
            ) b
           ) ab
      ) ab
where b_date is not null;

关于sql - 如果表A的值在表B中不可用，则从表B中选择先前的可用值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43890009/