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)))