根据有效日期选择相关记录的sql语句怎么写，请指导！

根据有效日期选择相关记录的sql语句如何写，请指导！......
table1: id,name,effdate
(1,aaa,2012-01-01)
(1,aaa,2012-03-10)
(1,aaa,2012-05-01)
(2,bbb,2012-02-01)
(2,bbb,2012-04-01)
......
table2: id,searchdate,...
(1,2012-02-02)
(1,2012-03-20)
...
(2,2012-03-02)
...

想要的结果是对应table2日期最近有效的table1记录。
如：
(1,2012-02-02,aaa,2012-01-01)
(1,2012-03-20,aaa,2012-03-10)
(2,2012-03-02,bbb,2012-02-01)
...

[解决办法]

SQL code

CREATE TABLE TABLE1(    Id INT,    Name VARCHAR(100),    Effdate DATETIME    )        GOINSERT INTO TABLE1SELECT 1,'aaa','2012-01-01' UNIONSELECT 1,'aaa','2012-03-10' UNIONSELECT 1,'aaa','2012-05-01' UNIONSELECT 2,'bbb','2012-02-01' UNIONSELECT 2,'bbb','2012-04-01' CREATE TABLE table2(  id INT,    searchdate DATETIME)        GO        INSERT INTO Table2    SELECT 1,'2012-02-02' UNIONSELECT 1,'2012-03-20' UNIONSELECT 2,'2012-03-02'WITH TableA AS(SELECT B.Id,searchdate,name,Effdate,DATEDIFF(DAY,Effdate,searchdate) AS DiffFROM TABLE1 AS A,Table2 AS BWHERE A.id = B.Id AND DATEDIFF(DAY,Effdate,searchdate) > 0)SELECT Id,searchdate,name,EffdateFROM TableA AS AWHERE Diff <= ALL(SELECT Diff FROM TableA  AS B WHERE A.Id = B.Id AND A.Effdate = B.Effdate)Id    searchdate            name    Effdate1    2012-02-02 00:00:00.000    aaa    2012-01-01 00:00:00.0001    2012-03-20 00:00:00.000    aaa    2012-03-10 00:00:00.0002    2012-03-02 00:00:00.000    bbb    2012-02-01 00:00:00.000
[解决办法]
SQL code
select a.id,b.searchdate,a.name,a.effdate from table1 a  left outer join table2  b on a.id=b.id    where  not exists(select 1 from table1 ,table2 where abs(datediff(dd,effdate,b.searchdate))<abs(datediff(dd,a.effdate,b.searchdate)))