取相近值关联问题，请各位前辈帮忙，谢谢

[carpenter01]

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
以此类推

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

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

[百年树人]

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 行受影响)
**/

[开着拖拉机泡妞]

--> 测试数据：[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
*/

[carpenter01]

[Quote=引用 1 楼 的回复:]
SQL code
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
s……
[/Quote]
可以达到预期结果，但是b表都在1w条的情况下执行很慢。
实际数据在10w左右，有其他办法？

[让你望见影子的墙]

产品代号=b.产品代号 and 生效日期<=采购日期

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

[carpenter01]

用 josy 写的子查询 方法，订单表中有多少条记录就会发起多少次子查询吧？

[昵称被占用了]

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

2005+

[昵称被占用了]

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

[carpenter01]

谢谢各位
我写个循环
以此update 订单表中的数据，实际操作比较快