求一SQL

[helphelpmeme]

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 15

B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
想要的结果为
id totalprice
1 30
2 30

[tim_spac]

/*
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    15 

B表中的内容是每次更改后的最新价格,日期.A表为出商品的出售日期.  
*/
set nocount on
go
create table table_A (
	id int,
	compDate datetime
)
insert into table_A
select 1,'2006-12-01' union
select 1,'2007-01-02' union
select 2,'2007-01-03' union
select 2,'2007-05-05'

create table table_B (
	id int,
	effiDate datetime,
	price numeric(10,2)
)
insert into table_B
select 1,'2006-05-09',20 union 
select 1,'2007-01-01',10 union 
select 2,'2007-01-01',15
go
/*
如何得到A表中全部的id信息,compDate,及在B表中effiDate以内的price
想要的结果为
 id   totalprice
 1    30
 2    30
*/
select id, totalPrice = sum(price)
from (
	select id, price = (select top 1 price from table_B b where a.id=b.id and effiDate<compDate order by effiDate desc)
	from table_A a) as a
group by id
go
drop table table_A
drop table table_B
go

[leo_lesley]

create table A(id int,compDate datetime)
insert A
select 1               ,'2006-12-01'
union select 1               ,'2007-01-02'   
union select 2               ,'2007-01-03'       
union select 2               ,'2007-05-05'  
go
create table B(id int,effiDate datetime,price int)
insert B
select 1           ,'2006-05-09',                   20   
union select 1               ,'2007-01-01',               10   
union select 2               ,'2007-01-01',               15   
go

select id,totalprice=sum(totalprice)
from (
select id,compDate,
totalprice=(select top 1 price from B where A.id=id and A.compDate>effiDate order by effiDate desc)
from A ) t
group by id

go
drop table A,B

/*  结果
id          totalprice  
----------- ----------- 
1           30
2           30

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