请教一条查询语句，很急，大家来帮忙

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

create table a(TransactionID varchar(5), Currency varchar(5), TransactionDate datetime);
insert into a(TransactionID, Currency, TransactionDate)
select
'0001','RMD','2009-5-4' union all select
'0002','RMD','2009-5-12' union all select 
'0003','RMD','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';create table b(Currency varchar(5), Rate decimal(5,1), EffectiveDate datetime);
insert into b(Currency, Rate, EffectiveDate)
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 a;
select * from b;

Answer 2

select *,
    (select top 1 rate from b where currency=a.currency 
         order by abs(datediff(day,a.TransactionDate,effectivedate)))
from a as a

Answer 3

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-11-05 21:09:30
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (TransactionID VARCHAR(4),Currency VARCHAR(3),TransactionDate DATETIME)
INSERT INTO [tb]
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'select * from [tb]
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-11-05 21:09:55
---------------------------------
 
IF OBJECT_ID('[tc]') IS NOT NULL 
    DROP TABLE [tc]
go
CREATE TABLE [tc] (Currency VARCHAR(3),Rate NUMERIC(2,1),EffectiveDate DATETIME)
INSERT INTO [tc]
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 [tc]select tb.*,rate=(select top 1 rate from tc where Currency=tb.Currency and EffectiveDate<TransactionDate order by EffectiveDate desc)
from tb0001 RMB 2009-05-04 00:00:00.000 1.3
0002 RMB 2009-05-12 00:00:00.000 1.3
0003 RMB 2009-06-08 00:00:00.000 1.3
0004 USD 2009-05-04 00:00:00.000 6.3
0005 USD 2009-05-12 00:00:00.000 6.3
0006 USD 2009-06-08 00:00:00.000 6.3

Answer 4

--> 测试数据：[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([TransactionID] varchar(4),[Currency] varchar(3),[TransactionDate] datetime)
insert [a1]
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'
--> 测试数据：[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2]([Currency ] varchar(3),[Rate] numeric(2,1),[EffectiveDate] datetime)
insert [b2]
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 *, rate=(select rate from b2 where  [Currency ]=t.[Currency] and [EffectiveDate]=(select max([EffectiveDate]) from b2 where   [EffectiveDate]<t.[TransactionDate]) )from a1 t/*
TransactionID Currency TransactionDate         rate
------------- -------- ----------------------- ---------------------------------------
0001          RMB      2009-05-04 00:00:00.000 1.3
0002          RMB      2009-05-12 00:00:00.000 1.3
0003          RMB      2009-06-08 00:00:00.000 1.3
0004          USD      2009-05-04 00:00:00.000 6.3
0005          USD      2009-05-12 00:00:00.000 6.3
0006          USD      2009-06-08 00:00:00.000 6.3*/

Answer 5

--> 测试数据: @ta
declare @ta table (TransactionID varchar(4),Currency varchar(3),TransactionDate datetime)
insert into @ta
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'
--> 测试数据: @tb
declare @tb table (Currency varchar(3),Rate numeric(2,1),EffectiveDate datetime)
insert into @tb
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 TransactionID, a.Currency,TransactionDate,
Rate=(select max(rate) from @tb where Currency=a.Currency and EffectiveDate<TransactionDate )   
 from @ta a
 
 TransactionID Currency TransactionDate         Rate
------------- -------- ----------------------- ---------------------------------------
0001          RMb      2009-05-04 00:00:00.000 1.3
0002          RMb      2009-05-12 00:00:00.000 1.3
0003          RMb      2009-06-08 00:00:00.000 1.3
0004          USD      2009-05-04 00:00:00.000 6.3
0005          USD      2009-05-12 00:00:00.000 6.3
0006          USD      2009-06-08 00:00:00.000 6.3(6 行受影响)

Answer 6

楼主的RMB,写成RMD，我说怎么结果老不对的呢

Answer 7

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-05 21:26:23
-- 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)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([TransactionID] varchar(4),[Currency] varchar(3),[TransactionDate] datetime)
insert [a]
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'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([Currency] varchar(3),[Rate] numeric(2,1),[EffectiveDate] datetime)
insert [b]
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'
--------------开始查询--------------------------
;with f as
(
select * from b t where not exists(select 1 from b where Currency=t.Currency and Rate>t.Rate)
)
select a.*,f.Rate from a join f on a.Currency=f.Currency
----------------结果----------------------------
/*TransactionID Currency TransactionDate         Rate
------------- -------- ----------------------- ---------------------------------------
0001          RMB      2009-05-04 00:00:00.000 1.3
0002          RMB      2009-05-12 00:00:00.000 1.3
0003          RMB      2009-06-08 00:00:00.000 1.3
0004          USD      2009-05-04 00:00:00.000 6.3
0005          USD      2009-05-12 00:00:00.000 6.3
0006          USD      2009-06-08 00:00:00.000 6.3(6 行受影响) 
*/

Answer 8

xiequan2，不好意思，点错了，没给你分。对不起啊