取相近值关联问题，请各位前辈帮忙，谢谢
a表--价格
产品代号 int,生效日期char(8) ,价格 int
... ...
1,20120101,100
1,20120303,300
1,20120501,500
... ....
2,20120101,1000
2,20120303,3000
2,20120501,5000
... ...
b表--订单
采购日期char(8),产品代号 int
20120112,1
20120212,1
20120312,1
20120505,1
20120112,2
20120212,2
20120312,2
20120505,2

问题订单表中没有价格，需要到价格表中去匹配。并且价格表中只有生效日期没有失效日期。

匹配规则
产品1 订单价格应为
20120101~20120302 的价格是 100
20120303~20120430 的价格是 300
20120501~ 的价格是 500
以此类推

真实数据量很大，并且生效日期也不是这么有规律的

怎样可以为订单表匹配到相应的价格？ 可以创建其他临时表只要最后得到 （采购日期，产品代号，价格）这样的结果就行

[解决办法]

SQL code

if object_id('[a]') is not null drop table [a]gocreate table [a]([产品代号] int,[生效日期] datetime,[价格] int)insert [a]select 1,'20120101',100 union allselect 1,'20120303',300 union allselect 1,'20120501',500 union allselect 2,'20120101',1000 union allselect 2,'20120303',3000 union allselect 2,'20120501',5000goif object_id('[b]') is not null drop table [b]gocreate table [b]([采购日期] datetime,[产品代号] int)insert [b]select '20120112',1 union allselect '20120212',1 union allselect '20120312',1 union allselect '20120505',1 union allselect '20120112',2 union allselect '20120212',2 union allselect '20120312',2 union allselect '20120505',2goselect b.*,  采购价格=(select top 1 价格 from a where 产品代号=b.产品代号 and 生效日期<=采购日期 order by 生效日期 desc)from b/**采购日期                    产品代号        采购价格----------------------- ----------- -----------2012-01-12 00:00:00.000 1           1002012-02-12 00:00:00.000 1           1002012-03-12 00:00:00.000 1           3002012-05-05 00:00:00.000 1           5002012-01-12 00:00:00.000 2           10002012-02-12 00:00:00.000 2           10002012-03-12 00:00:00.000 2           30002012-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 allselect 1,'20120303',300 union allselect 1,'20120501',500 union allselect 2,'20120101',1000 union allselect 2,'20120303',3000 union allselect 2,'20120501',5000--> 测试数据：[订单]if object_id('[订单]') is not null drop table [订单]create table [订单]([采购日期] datetime,[产品代号] int)insert [订单]select '20120112',1 union allselect '20120212',1 union allselect '20120312',1 union allselect '20120505',1 union allselect '20120112',2 union allselect '20120212',2 union allselect '20120312',2 union allselect '20120505',2select 采购日期 ,产品代号, 价格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)twhere px=1order by 产品代号,采购日期/*采购日期    产品代号    价格2012-01-12 00:00:00.000    1    1002012-02-12 00:00:00.000    1    1002012-03-12 00:00:00.000    1    3002012-05-05 00:00:00.000    1    5002012-01-12 00:00:00.000    2    10002012-02-12 00:00:00.000    2    10002012-03-12 00:00:00.000    2    30002012-05-05 00:00:00.000    2    5000*/
[解决办法]
 产品代号=b.产品代号 and 生效日期<=采购日期

在上述几个列上建立索引，试试
[解决办法]
SQL code
select b.*,  采购价格=t.价格from bouter apply (  select top 1 价格 from a where 产品代号=b.产品代号 and 生效日期<=a.采购日期 order by 生效日期 desc) as t
[解决办法]
b表需要（产品代号，生效日期 desc）索引，最好聚集
a表需要（产品代号）索引，最好聚集