高手来帮忙，这个update语句怎么写？

[hcl8260]

大家好。我现在遇到一个问题，表1中存储intid、日期、价格。其中同一个intid有多个不同的日期及价格。 表2中的数据是intid，入库日期，价格。其中价格是空值。现在要求update表2，使其价格＝表1中的价格。条件是：表1中的日期<=表2中的入库日期（有可能是多条），相差天数最小的那条记录的价格。
以下是测试临时表的sql：
--==============================================================================
create table #t1
(
intid int not null,
datep smalldatetime,
price decimal(19,2) null
)

insert into #t1 select 1,'2008-8-1', 1.1
insert into #t1 select 1,'2008-9-1', 1.2
insert into #t1 select 2,'2008-9-1', 2.1
insert into #t1 select 2,'2008-10-1', 2.2
insert into #t1 select 2,'2008-10-10', 2.2

create table #t2
(
intid int not null,
indate smalldatetime,
price decimal(19,2) null
)

insert into #t2 (intid,indate) select 1, '2008-9-10'
insert into #t2 (intid,indate) select 2, '2008-10-8'
--========================================================================
update之后，#t2中的数据应该为 1, '2008-9-10',1.2
2, '2008-10-8',2.2

请各位高人帮忙，在线等，谢谢！！

[hcl8260]

重新发一下sql，其中有一条写错了
create table #t1
(
intid int not null,
datep smalldatetime,
price decimal(19,2) null
)

insert into #t1 select 1,'2008-8-1', 1.1
insert into #t1 select 1,'2008-9-1', 1.2
insert into #t1 select 2,'2008-9-1', 2.1
insert into #t1 select 2,'2008-10-1', 2.2
insert into #t1 select 2,'2008-10-10', 2.3

create table #t2
(
intid int not null,
indate smalldatetime,
price decimal(19,2) null
)

insert into #t2 (intid,indate) select 1, '2008-9-10'
insert into #t2 (intid,indate) select 2, '2008-10-8'

[dawugui]

create table #t1 
( 
intid int not null, 
datep smalldatetime, 
price decimal(19,2) null 
) 

insert into #t1 select 1,'2008-8-1', 1.1 
insert into #t1 select 1,'2008-9-1', 1.2 
insert into #t1 select 2,'2008-9-1', 2.1 
insert into #t1 select 2,'2008-10-1', 2.2 
insert into #t1 select 2,'2008-10-10', 2.2 

create table #t2 
( 
intid int not null, 
indate smalldatetime, 
price decimal(19,2) null 
) 

insert into #t2 (intid,indate) select 1, '2008-9-10' 
insert into #t2 (intid,indate) select 2, '2008-10-8' 

update #t2 set price = (select top 1 price from #t1 n where n.intid = m.intid and n.datep < m.indate order by n.datep desc) from #t2 m

select * from #t2

drop table #t1, #t2

/*
intid       indate                                                 price                 
----------- ------------------------------------------------------ --------------------- 
1           2008-09-10 00:00:00                                    1.20
2           2008-10-08 00:00:00                                    2.20

（所影响的行数为 2 行）
*/

[dawugui]

[Quote=引用 1 楼 hcl8260 的回复:]
重新发一下sql，其中有一条写错了
create table #t1
(
intid int not null,
datep smalldatetime,
price decimal(19,2) null
)

insert into #t1 select 1,'2008-8-1', 1.1
insert into #t1 select 1,'2008-9-1', 1.2
insert into #t1 select 2,'2008-9-1', 2.1
insert into #t1 select 2,'2008-10-1', 2.2
insert into #t1 select 2,'2008-10-10', 2.3

create table #t2
(
intid int not null,
indate smalldatetime,
price decimal(19,2) null
)

insert into #t2 (intid,indate) select 1, '2008-9-10'
insert into #t2 (intid,indate) select 2, '2008-10-8'
[/Quote]

create table #t1 
( 
intid int not null, 
datep smalldatetime, 
price decimal(19,2) null 
) 

insert into #t1 select 1,'2008-8-1', 1.1 
insert into #t1 select 1,'2008-9-1', 1.2 
insert into #t1 select 2,'2008-9-1', 2.1 
insert into #t1 select 2,'2008-10-1', 2.2 
insert into #t1 select 2,'2008-10-10', 2.3 

create table #t2 
( 
intid int not null, 
indate smalldatetime, 
price decimal(19,2) null 
) 

insert into #t2 (intid,indate) select 1, '2008-9-10' 
insert into #t2 (intid,indate) select 2, '2008-10-8' 


update #t2 set price = (select top 1 price from #t1 n where n.intid = m.intid and n.datep < m.indate order by n.datep desc) from #t2 m

select * from #t2

drop table #t1, #t2

/*
intid       indate                                                 price                 
----------- ------------------------------------------------------ --------------------- 
1           2008-09-10 00:00:00                                    1.20
2           2008-10-08 00:00:00                                    2.20

（所影响的行数为 2 行）


*/

[hcl8260]

谢谢！！！！！呵呵，我咋没想到呢