if object_id('[a]') is not null drop table [a]
go
create table [a]([产品代号] int,[生效日期] datetime,[价格] int)
insert [a]
select 1,'20120101',100 union all
select 1,'20120303',300 union all
select 1,'20120501',500 union all
select 2,'20120101',1000 union all
select 2,'20120303',3000 union all
select 2,'20120501',5000
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([采购日期] datetime,[产品代号] int)
insert [b]
select '20120112',1 union all
select '20120212',1 union all
select '20120312',1 union all
select '20120505',1 union all
select '20120112',2 union all
select '20120212',2 union all
select '20120312',2 union all
select '20120505',2
go

select b.*,
  采购价格=(select top 1 价格 from a where 产品代号=b.产品代号 and 生效日期<=采购日期 order by 生效日期 desc)
from b

/**
采购日期                    产品代号        采购价格
----------------------- ----------- -----------
2012-01-12 00:00:00.000 1           100
2012-02-12 00:00:00.000 1           100
2012-03-12 00:00:00.000 1           300
2012-05-05 00:00:00.000 1           500
2012-01-12 00:00:00.000 2           1000
2012-02-12 00:00:00.000 2           1000
2012-03-12 00:00:00.000 2           3000
2012-05-05 00:00:00.000 2           5000

(8 行受影响)
**/

------解决方案--------------------
SQL code

--> 测试数据：[test]
if OBJECT_ID('[test]')is not null drop table [test]
create table [test]([产品代号] int,[生效日期] datetime,[价格] int)
insert [test]
select 1,'20120101',100 union all
select 1,'20120303',300 union all
select 1,'20120501',500 union all
select 2,'20120101',1000 union all
select 2,'20120303',3000 union all
select 2,'20120501',5000
--> 测试数据：[订单]
if object_id('[订单]') is not null drop table [订单]
create table [订单]([采购日期] datetime,[产品代号] int)
insert [订单]
select '20120112',1 union all
select '20120212',1 union all
select '20120312',1 union all
select '20120505',1 union all
select '20120112',2 union all
select '20120212',2 union all
select '20120312',2 union all
select '20120505',2



select 采购日期 ,产品代号, 价格
from(
select px=row_number()over(partition by b.采购日期,a.产品代号
order by datediff(dd,a.生效日期,b.采购日期) asc),
a.产品代号,b.采购日期,a.价格,
datediff(dd,a.生效日期,b.采购日期)as days from [test] a cross join [订单] b 
where a.产品代号=b.产品代号 and datediff(dd,a.生效日期,b.采购日期)>=0)t
where px=1
order by 产品代号,采购日期

/*
采购日期    产品代号    价格
2012-01-12 00:00:00.000    1    100
2012-02-12 00:00:00.000    1    100
2012-03-12 00:00:00.000    1    300
2012-05-05 00:00:00.000    1    500
2012-01-12 00:00:00.000    2    1000
2012-02-12 00:00:00.000    2    1000
2012-03-12 00:00:00.000    2    3000
2012-05-05 00:00:00.000    2    5000
*/

------解决方案--------------------
 产品代号=b.产品代号 and 生效日期<=采购日期

在上述几个列上建立索引，试试

------解决方案--------------------
SQL code
select b.*,
  采购价格=t.价格
from b
outer apply (
  select top 1 价格 from a where 产品代号=b.产品代号 and 生效日期<=a.采购日期 order by 生效日期 desc) as t

------解决方案--------------------
b表需要（产品代号，生效日期 desc）索引，最好聚集
a表需要（产品代号）索引，最好聚集