求一SQL语句，先谢了！该如何解决

求一SQL语句，先谢了！
有如下两Table：
A:
Code Rate StartDate
USD 8.0 2007-01-01
USD 7.85 2007-06-01
USD 7.75 2007-08-01
USD 7.60 2007-10-01

B:
PCode Currency Price EnabledDate
ABC USD 1.55 2007-02-08
ABC USD 1.52 2007-07-10
ABC USD 1.50 2007-08-15
ABC USD 1.45 2007-10-18

要查出以下结果：

PCode Currency Rate PriceRMB EnabledDate
----- -------- ---- -------- -----------
ABC USD 8.0 12.40 2007-02-08
ABC USD 7.85 11.932 2007-07-10
ABC USD 7.75 11.625 2007-08-15
ABC USD 7.60 11.02 2007-10-18

请问怎么写这一SQL语句？

------解决方案--------------------
１楼的少个条件。

SQL code

select PCode, Currency 
    ,Rate=(select top 1 Rate from A where Code=B.Code and StartDate<EnabledDate order by StartDate desc)
    ,PriceRMB=(select top 1 Rate from A where Code=B.Code and StartDate<EnabledDate order by StartDate desc)*Price
    ,EnabledDate 
from B

------解决方案--------------------
写法就是这样,随手敲的,不排除手误.
当然,也可以用临时表按startdate和enabledate分别生成identity列,然后用identity列去连表

SQL code

select b.pcode,b.currency,a.rate,b.price*a.rate priceRMB,b.enabledDate
   from tB b
inner join tA a
   on
   (select count(*) from tB x where x.EnabledDate<b.EnabledDate)
   =
   (select count(*) from tA x where x.StartDate<a.StartDate)

------解决方案--------------------

SQL code


select id= identity(int,1,1),* into #tb1 from ta
select id= identity(int,1,1),* into #tb2 from tb
select b. pcode,b.currency,a.rate,pricermb=(a.rate*b.price),b.enabledate from #tb1 a on #tb2 b on a.code=b.currency

------解决方案--------------------


select B.PCode ,B.Currency,A.Rate ,(A.Rate*B.Price)as PriceRMB,B.EnabledDate from A,B
where A.Currency=B.Currency

求一SQL语句，先谢了！该如何解决

相关推荐