求一SQL

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 行）

*/

/*

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