请教一个SQL查询，以Time字段最接近为原则，将两个表合并
这个问题我想破头都想不出来，请教高手帮忙

有两个表
Table1，有一个Time字段
Table2，有两个字段，Time和Value

我想把这两个表合并起来，合并原则是以Table1的Time和Table2的Time最接近
我想不出来，急死了，高手帮帮忙

------解决方案--------------------
/*
OK了
*/

declare @Table1 table(Time datetime)
insert @Table1 select '2007-9-4 8:00:00 '
insert @Table1 select '2007-9-4 9:00:00 '

declare @Table2 table(Time datetime,Value money)
insert @Table2 select '2007-9-4 8:55:00 ', 1.0
insert @Table2 select '2007-9-4 8:05:00 ', 2.0

select a.*,b.* from @Table1 a full join
(select Time1=a.Time, diff = min(abs(datediff(ms,a.Time,b.Time))) from @Table1 a, @Table2 b group by a.Time) c
on a.Time=c.Time1
full join @Table2 b
on b.Time = dateadd(ms,c.diff,a.Time) or b.Time = dateadd(ms,-c.diff,a.Time)

------解决方案--------------------
create table TableA
(
ID int identity(1,1)
,TimeLock datetime
,name nvarchar(10)
)

go

create table TableB
(
ID int identity(1,1)
,TimeLock datetime
,name nvarchar(10)
)

go


insert TableA (TimeLock,Name)
select '2004-1-1 ', 'AAA1 ' union
select '2004-2-1 ', 'AAA2 ' union
select '2004-3-1 ', 'AAA3 ' union
select '2004-4-1 ', 'AAA4 '


insert TableB (TimeLock,Name)
select '2004-1-10 ', 'AAA1 ' union
select '2004-2-25 ', 'AAA2 ' union
select '2004-3-2 ', 'AAA3 ' union
select '2004-4-2 ', 'AAA4 '


select d.* from

(
select a.ID as IDA,min(ABS(datediff(second,a.timelock,b.timelock))) as different from TableA a left Outer join TableB b on 1 = 1 group by a.ID
) c

inner join
(
select a.ID as IDA, a.Timelock as ATimeLock, a.Name as AName,b.ID as IDB,b.TimeLock as BTimeLock,b.Name as BName,ABS(datediff(second,a.timelock,b.timelock)) as different from TableA a left Outer join TableB b on 1 = 1
) d
on c.different = d.different