A表中有字段id,compDate
1 2006-12-01
1 2007-01-02
2 2007-01-03
2 2007-05-05
B表中有 id,effiDate price
1 2006-05-09 20
1 2007-01-01 10
2 2007-01-01 15B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
1 2006-12-01
1 2007-01-02
2 2007-01-03
2 2007-05-05
B表中有 id,effiDate price
1 2006-05-09 20
1 2007-01-01 10
2 2007-01-01 15B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
id compDate price
1 2006-12-01 20
1 2007-01-02 10
2 2007-01-03 15
2 2007-05-05 15
select
*
,price=(select top 1 price from B表 where B表.id=A表.id and B表.effiDate<=A表.compDate order by effiDate desc)
from A表
insert into @table_a(id,compDate) values (1,'2006-12-01')
insert into @table_a(id,compDate) values (2,'2007-01-02')
insert into @table_a(id,compDate) values (3,'2007-01-03')
insert into @table_a(id,compDate) values (4,'2007-01-05')
declare @table_b table(id int ,effiDate datetime,price int)
insert into @table_b (id,effiDate,price) values (1, '2006-05-09',20)
insert into @table_b (id,effiDate,price) values (2, '2007-01-01',10)
insert into @table_b (id,effiDate,price) values (3, '2007-01-01',15)declare @price intselect @price=price from (select top 1 price from @table_b order by id desc) as cselect a.*,
isnull(price,@price)
from @table_a a
left join
@table_b b on a.id=b.id