表a (code为主键)code date value
1 2009-1-1 123
...表b (code和date为主键)
code date value
1 2008-1-1 44
1 2008-7-1 55
...现在连接表a和b,条件如下:
a中的记录全要。
a中每录记录最多只连接b中一条,要求code相等,时间为不超过a.date的最大值。如果没有相对应的b记录,则为NULL。
1 2009-1-1 123
...表b (code和date为主键)
code date value
1 2008-1-1 44
1 2008-7-1 55
...现在连接表a和b,条件如下:
a中的记录全要。
a中每录记录最多只连接b中一条,要求code相等,时间为不超过a.date的最大值。如果没有相对应的b记录,则为NULL。
SELECT AA.CODE,AA.DATE,AA.value,B.code,B.date,B.value
FROM B,
(SELECT A.CODE,A.DATE,A.value,ROW_NUMBER()OVER(PARTITION BY A.CODE ORDER BY A.DATE DESC) AS CNT
FROM A) AS AA
WHERE AA.CNT=1
AND AA.CODE=B.CODE
AND B.DATE <=AA.DATE
from a left join b b1
on a.code=b1.code and b1.[date] = (select max([date]) from b where code=a.code and [date]<=a.[date])
(
code int,
date1 datetime,
value int
)
create table #E
(
code int,
date1 datetime,
value int
)
insert into #W
select 1,'2009-1-1',123
insert into #E
select 1,'2008-1-1',44 union all
select 1,'2008-7-1' ,55
select C.* from
(select code,MAX(date2) date2 from
(select #W.*,(#E.date1) date2,(#E.value) value2 from #W,#E where #W.code=#E.code) A
where date1>date2
group by code) B,(select #W.*,(#E.date1) date2,(#E.value) value2 from #W,#E where #W.code=#E.code) C
where B.code=C.code and B.date2=C.date2
insert into a values(1, '2009-1-1', 123)
insert into a values(2 , '2009-1-2', 234)create table b(code int, date varchar(10), value int)
insert into b values(1, '2008-1-1', 44)
insert into b values(1, '2008-7-1', 55 )SELECT a.code, a.date, a.value, b.date AS bdate, MAX(b.value) AS bvalue
FROM a LEFT OUTER JOIN
b ON a.code = b.code AND a.date > b.date
GROUP BY a.code, a.date, a.value, b.date
(select top 1 b.date from b where b.code = a.code and b.date <= a.date order by b.date desc),
(select top 1 b.value from b where b.code = a.code and b.date <= a.date order by b.date desc)
from a
a left Join b
on a.code=b.code
and b.date=(select max(b.date) From a left Join b on a.code=b.code and b.date<a.date)
---------------------------
1 2009-1-1 123 1 2008-7-1 55
2 2009-1-2 234 NULL NULL NULL