CREATE TABLE TABLE1
(
    Id INT,
    Name VARCHAR(100),
    Effdate DATETIME
    )
    
    GO

INSERT INTO TABLE1
SELECT 1,'aaa','2012-01-01' UNION
SELECT 1,'aaa','2012-03-10' UNION
SELECT 1,'aaa','2012-05-01' UNION
SELECT 2,'bbb','2012-02-01' UNION
SELECT 2,'bbb','2012-04-01' 


CREATE TABLE table2
(  id INT,
    searchdate DATETIME)
    
    GO
    
    INSERT INTO Table2
    SELECT 1,'2012-02-02' UNION
SELECT 1,'2012-03-20' UNION
SELECT 2,'2012-03-02'



WITH TableA AS
(SELECT B.Id,searchdate,name,Effdate,DATEDIFF(DAY,Effdate,searchdate) AS Diff
FROM TABLE1 AS A,Table2 AS B
WHERE A.id = B.Id AND DATEDIFF(DAY,Effdate,searchdate) > 0)

SELECT Id,searchdate,name,Effdate
FROM TableA AS A
WHERE Diff <= ALL(SELECT Diff FROM TableA  AS B WHERE A.Id = B.Id AND A.Effdate = B.Effdate)


Id    searchdate            name    Effdate
1    2012-02-02 00:00:00.000    aaa    2012-01-01 00:00:00.000
1    2012-03-20 00:00:00.000    aaa    2012-03-10 00:00:00.000
2    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)))