如何取得每次交易的汇率？

Question

有2张表，表1存放交易数据，表2存放汇率数据。例如：表1：TransactionID（主键）        Currency          TransactionDate
0001                         RMD               2009-5-4
0002                         RMD               2009-5-12
0003                         RMD               2009-6-8
0004                         USD               2009-5-4
0005                         USD               2009-5-12
0006                         USD               2009-6-8表2：
Currency（主键）            Rate               EffectiveDate(主键)
RMB                         1.2                2009-4-1
RMB                         1.3                2009-5-1
USD                         6.2                2009-4-1
USD                         6.3                2009-5-1现在要求将表1中的交易数据的汇率查出。
表2中小于表1中TransactionDate且最接近TransactionDate的那个个EffectiveDate对应的Rate即为交易汇率。结果如下：
TransactionID（主键）        Currency          TransactionDate             Rate
0001                         RMD               2009-5-4                    1.3
0002                         RMD               2009-5-12                   1.3
0003                         RMD               2009-6-8                    1.3
0004                         USD               2009-5-4                    6.3
0005                         USD               2009-5-12                   6.3
0006                         USD               2009-6-8                    6.3请教如何用SQL实现！

Answer 1

两表用min(时间差)连接就OK了！

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-08 23:04:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[表1]
if object_id('[表1]') is not null drop table [表1]
go 
create table [表1]([TransactionID] varchar(4),[Currency] varchar(3),[TransactionDate] datetime)
insert [表1]
select '0001','RMB','2009-5-4' union all
select '0002','RMB','2009-5-12' union all
select '0003','RMB','2009-6-8' union all
select '0004','USD','2009-5-4' union all
select '0005','USD','2009-5-12' union all
select '0006','USD','2009-6-8'
--> 测试数据：[表2]
if object_id('[表2]') is not null drop table [表2]
go 
create table [表2]([Currency] varchar(3),[Rate] numeric(2,1),[EffectiveDate] datetime)
insert [表2]
select 'RMB',1.2,'2009-4-1' union all
select 'RMB',1.3,'2009-5-1' union all
select 'USD',6.2,'2009-4-1' union all
select 'USD',6.3,'2009-5-1'
--------------开始查询--------------------------
select
 * 
from
 [表1] a
join
 (select * from [表2] t where not exists(select 1 from [表2] where Currency=t.Currency and EffectiveDate>t.EffectiveDate))b
on
 a.Currency=b.Currency
----------------结果----------------------------
/*TransactionID Currency TransactionDate         Currency Rate                                    EffectiveDate
------------- -------- ----------------------- -------- --------------------------------------- -----------------------
0001          RMB      2009-05-04 00:00:00.000 RMB      1.3                                     2009-05-01 00:00:00.000
0002          RMB      2009-05-12 00:00:00.000 RMB      1.3                                     2009-05-01 00:00:00.000
0003          RMB      2009-06-08 00:00:00.000 RMB      1.3                                     2009-05-01 00:00:00.000
0004          USD      2009-05-04 00:00:00.000 USD      6.3                                     2009-05-01 00:00:00.000
0005          USD      2009-05-12 00:00:00.000 USD      6.3                                     2009-05-01 00:00:00.000
0006          USD      2009-06-08 00:00:00.000 USD      6.3                                     2009-05-01 00:00:00.000(6 行受影响) 
*/