表A:id transdate
ac 2008-12-23
ad 2009-01-24表B:
transdate price
2008-11-01 3000
2008-11-03 3800
2009-01-02 3200
2009-01-24 4000
2009-01-25 4000
取出表B中的記錄,條件是表B中的日期《=表A中日期,并且最近的那一條,,當然也可以是相等的
效果如下:
id transdate price
ac 2008-11-03 3800
ad 2009-01-24 4000
ac 2008-12-23
ad 2009-01-24表B:
transdate price
2008-11-01 3000
2008-11-03 3800
2009-01-02 3200
2009-01-24 4000
2009-01-25 4000
取出表B中的記錄,條件是表B中的日期《=表A中日期,并且最近的那一條,,當然也可以是相等的
效果如下:
id transdate price
ac 2008-11-03 3800
ad 2009-01-24 4000
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (id varchar(2),transdate datetime)
insert into #ta
select 'ac','2008-12-23' union all
select 'ad','2009-01-24'
--> 测试数据: #tB
if object_id('tempdb.dbo.#tB') is not null drop table #tB
go
create table #tB (transdate datetime,price int)
insert into #tB
select '2008-11-01',3000 union all
select '2008-11-03',3800 union all
select '2009-01-02',3200 union all
select '2009-01-24',4000 union all
select '2009-01-25',4000
;with cte as
(
select a.id,a.transdate as tr,b.*,n= DATEDIFF(DAY,b.transdate,a.transdate) from #tB b,#ta a
where DATEDIFF(DAY,b.transdate,a.transdate)>=0
)
select t.id,transdate,price from cte t
where not exists( select * from cte where tr=t.tr and n<t.n )
/*
id transdate price
---- ----------------------- -----------
ac 2008-11-03 00:00:00.000 3800
ad 2009-01-24 00:00:00.000 4000(2 行受影响)
*/
insert into @表A
select 'ac','2008-12-23' union all
select 'ad','2009-01-24'declare @表B table (transdate datetime,price int)
insert into @表B
select '2008-11-01',3000 union all
select '2008-11-03',3800 union all
select '2009-01-02',3200 union all
select '2009-01-24',4000 union all
select '2009-01-25',4000;with maco as(
select row_number() over (order by transdate ) as rowid,*
from
(
select transdate,0 as price ,1 as col from @表A union all
select transdate,price,2 from @表B
)aa)select b.transdate,b.price from maco a
left join maco b on a.rowid=b.rowid+1
where a.col=1/*
transdate price
----------------------- -----------
2008-11-03 00:00:00.000 3800
2009-01-24 00:00:00.000 4000
*/
go
create table #ta (id varchar(2),transdate datetime)
insert into #ta
select 'ac','2008-12-23' union all
select 'ad','2009-01-24'
--> 测试数据: #tB
if object_id('tempdb.dbo.#tB') is not null drop table #tB
go
create table #tB (transdate datetime,price int)
insert into #tB
select '2008-11-01',3000 union all
select '2008-11-03',3800 union all
select '2009-01-02',3200 union all
select '2009-01-24',4000 union all
select '2009-01-25',4000--test
select d.id, d.btransdate, d.price from
(
select number = ROW_NUMBER() over(partition by c.id order by c.diff desc), * from
(
select a.id, atransdate=a.transdate, btransdate = b.transdate, b.price, diff = DATEDIFF(DAY, a.transdate, b.transdate) from #ta a, #tb b
) c where diff <= 0
) d
where d.number = 1
insert into tbA select 'ac','2008-12-23'
insert into tbA select 'ad','2009-01-24'
create table tbB(transdate datetime,price int)
insert into tbB select '2008-11-01',3000
insert into tbB select '2008-11-03',3800
insert into tbB select '2009-01-02',3200
insert into tbB select '2009-01-24',4000
insert into tbB select '2009-01-25',4000
go
select id,transdate,
(select top 1 price from tbB where transdate<=a.transdate order by transdate desc)price
from tbA a
go
drop table tbA,tbB
/*
id transdate price
---------- ----------------------- -----------
ac 2008-12-23 00:00:00.000 3800
ad 2009-01-24 00:00:00.000 4000(2 行受影响)
*/