根据有效日期选择相关记录的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
    )
    
    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)))

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

相关推荐