sql 两表时间就近匹配

Question

表a：
 dt                   aa
2009-10-30             5
2009-10-29             6
2009-10-28             7
2009-10-27             4
2009-10-26             5 
2009-10-25             5
2009-10-24             7
2009-10-23             8
2009-10-22             5
2009-10-21             6
2009-10-20             7
2009-10-19             6
2009-10-18             8
2009-10-17             3
2009-10-16             2表b
dt               bb
2009-10-27       2
2009-10-20       3
2009-10-16       2
怎样查出结果：
2009-10-30             5       2009-10-27       2
2009-10-29             6       2009-10-27       2
2009-10-28             7       2009-10-27       2
2009-10-27             4       2009-10-27       2
2009-10-26             5       2009-10-20       3 
2009-10-25             5       2009-10-20       3
2009-10-24             7       2009-10-20       3
2009-10-23             8       2009-10-20       3
2009-10-22             5       2009-10-20       3
2009-10-21             6       2009-10-20       3
2009-10-20             7       2009-10-20       3
2009-10-19             6       2009-10-16       2
2009-10-18             8       2009-10-16       2
2009-10-17             3       2009-10-16       2
2009-10-16             2       2009-10-16       2

Answer 1

select a.dt, a.aa, b.dt, b.bb
  from a, b
 where a.dt - b.dt >= 0
 order by b.dt desc, a.dt

Answer 2

select b1.rq,b1.id,b2.rq,b2.id   from (
select t.rq,t.id, row_number()over(order by t. rq) seq1  from  biao1 t ) b1 ,
(select t.rq,t.id, row_number()over(order by t. rq) seq2  from  biao2 t) b2
where b1.seq1=b2.seq2  这样试试

Answer 3

select *
from a t1,b t2
where not exists(
  select 1 from b
    where abs(dt-a.dt)<abs(b.dt-a.dt))

Answer 4

别名对错了select * 
from a t1,b t2 
where not exists( 
  select 1 from b 
    where abs(dt-t1.dt) <abs(t2.dt-t1.dt))