求一SQL 语句，关于INNER JOIN的条件，最后10分了，有了分再送200！！！一定！！！

Question

一个表有一列是日期，比如02/11/2006
03/11/2006
04/11/2006
15/11/2006另一个表有一列也是日期，比如03/11/2006
16/11/2006怎么写一SQL语句让连个表联合，条件是第一个表最接近且小于第二个表的列，如：
02/11/2006   03/11/2006
03/11/2006   03/11/2006
04/11/2006   16/11/2006
15/11/2006   16/11/2006最后10分了，多谢，等着写好下班回家了。

Answer 1

select t1.date,t2.date from t1 inner join t2 on t1.date<=t2.date

Answer 2

怎么保证和t1.date匹配的t2.date是最接近t1.date的呢？如果 t2里面有个 05/11/2006怎么保证和 04/11/2006 匹配的是 05/11/2006 而不是 16/11/2006

Answer 3

declare @t1 table (date datetime)
declare @t2 table (date datetime)insert into @t1 
select convert( datetime,'02/11/2006' ,103 ) union all 
select convert( datetime,'03/11/2006' ,103 ) union all 
select convert( datetime,'04/11/2006' ,103 ) union all 
select convert( datetime,'07/11/2006' ,103 ) union all 
select convert( datetime,'15/11/2006' ,103 )insert into @t2 
select convert( datetime,'04/11/2006' ,103 ) union all 
select convert( datetime,'05/11/2006' ,103 ) union all 
select convert( datetime,'16/11/2006' ,103 )select t1.date,(select min(t2.date) from @t2 t2 where t2.date >t1.date )
from @t1 t1 date                                                                                                          
------------------------------------------------------ ------------------------------------------------------ 
2006-11-02 00:00:00.000                                2006-11-04 00:00:00.000
2006-11-03 00:00:00.000                                2006-11-04 00:00:00.000
2006-11-04 00:00:00.000                                2006-11-05 00:00:00.000
2006-11-07 00:00:00.000                                2006-11-16 00:00:00.000
2006-11-15 00:00:00.000                                2006-11-16 00:00:00.000（所影响的行数为 5 行）

Answer 4

select t1.date,t2.date from t1 inner join t2 on 
   min(datediff(day,'t1.date','t2.date'))                              
 

Answer 5

declare @t1 table (date datetime)
declare @t2 table (date datetime)insert into @t1 
select convert( datetime,'02/11/2006' ,103 ) union all 
select convert( datetime,'03/11/2006' ,103 ) union all 
select convert( datetime,'04/11/2006' ,103 ) union all 
select convert( datetime,'07/11/2006' ,103 ) union all 
select convert( datetime,'15/11/2006' ,103 )insert into @t2 
select convert( datetime,'04/11/2006' ,103 ) union all 
select convert( datetime,'05/11/2006' ,103 ) union all 
select convert( datetime,'16/11/2006' ,103 )
select t1.date as date1,date2=(select top 1 date from @t2 t2 where t2.date>t1.date order by date asc) from @t1 t1（所影响的行数为 5 行）
（所影响的行数为 3 行）date1                                                  date2                                                  
------------------------------------------------------ ------------------------------------------------------ 
2006-11-02 00:00:00.000                                2006-11-04 00:00:00.000
2006-11-03 00:00:00.000                                2006-11-04 00:00:00.000
2006-11-04 00:00:00.000                                2006-11-05 00:00:00.000
2006-11-07 00:00:00.000                                2006-11-16 00:00:00.000
2006-11-15 00:00:00.000                                2006-11-16 00:00:00.000（所影响的行数为 5 行）