求一SQL

Question

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

Answer 1

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

Answer 2

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   
goselect 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 idgo
drop table A,B/*  结果
id          totalprice  
----------- ----------- 
1           30
2           30（所影响的行数为 2 行）
*/