如ta
PRCDate Amount ReportNo Cru
03/22/2009 20 a01 USD
05/05/2010 30 a02 HKD
08/22/2010 40 a03 RMB
08/22/2010 40 a03 USDtb
years Month Cru Rates
2009 03 USD 6.5674
2010 05 HKD 0.8676
2010 08 USD 6.8992分割ta中PRCDate字段做条件(ta.PRCDate.mm=tb.month and ta.prcdate.yyyy=tb.years and ta.cru=tb.cru)
得到:
PRCDate Amount ReportNo Cru Rates
03/22/2009 20 a01 USD 6.5674
05/05/2010 30 a02 HKD 0.8676
08/22/2010 40 a03 RMB 1
08/22/2010 40 a03 USD 6.8992
PRCDate Amount ReportNo Cru
03/22/2009 20 a01 USD
05/05/2010 30 a02 HKD
08/22/2010 40 a03 RMB
08/22/2010 40 a03 USDtb
years Month Cru Rates
2009 03 USD 6.5674
2010 05 HKD 0.8676
2010 08 USD 6.8992分割ta中PRCDate字段做条件(ta.PRCDate.mm=tb.month and ta.prcdate.yyyy=tb.years and ta.cru=tb.cru)
得到:
PRCDate Amount ReportNo Cru Rates
03/22/2009 20 a01 USD 6.5674
05/05/2010 30 a02 HKD 0.8676
08/22/2010 40 a03 RMB 1
08/22/2010 40 a03 USD 6.8992
解决方案 »
- servicebroker的问题
- 15毫秒insert一笔数据,用Access数据库行不行?
- 简单吗?MS SQL 2000 没有备份过,可以通过日志来还原吗?
- 高手请指教:批量添加规律性的记录
- [重开一帖,解决后再追加100分]强制还原后,数据库物理文件名与逻辑文件名不一致
- 关于用SQL script 实现replication的问题
- 一个表有了记录之后,如何删除一个列?
- 如何提出同一天的数据?
- 快快帮帮我!我都急死了,哪位高手可以指点一下:在SQLSERVER 桌面版下是不是不能进行数据库备份?
- 从表(有几亿条数据)删除和插入大量(几百万条)的数据,有什么好的建议?
- 内存中还是数据库中
- sql存储过程导入oracle里
datepart(mm,ta.PRCDate)=convert(int,tb.month)
and DATENAME(year,ta.prcdate) = tb.years
and ta.cru=tb.cru
if OBJECT_ID('ta') is not null
drop table ta
go
create table ta (PRCDate date,Amount int,ReportNo varchar(3),Cru varchar(3))
insert into ta
select '03/22/2009', 20, 'a01', 'USD' union all
select '05/05/2010', 30, 'a02', 'HKD' union all
select '08/22/2010', 40, 'a03', 'RMB' union all
select '08/22/2010', 40, 'a03', 'USD'
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (years int,Month varchar(2),Cru varchar(3),Rates numeric(10,4))
insert into tb
select '2009', '03', 'USD', 6.5674 union all
select '2010', '05', 'HKD', 0.8676 union all
select '2010', '08', 'USD', 6.8992
select a.PRCDate,a.Amount,a.ReportNo,isnull(b.Rates,1) as Rates
from ta a left join tb b on DATEPART(YEAR,a.PRCDate)=b.years
and DATEPART(MONTH,a.PRCDate)=cast(b.Month as int)
and a.Cru=b.Cru
PRCDate Amount ReportNo Rates
2009-03-22 20 a01 6.5674
2010-05-05 30 a02 0.8676
2010-08-22 40 a03 1.0000
2010-08-22 40 a03 6.8992
-trydeclare @ta table (prcdate datetime,amount int,reportno nvarchar(10),cru nvarchar(10))
insert into @ta select '03/22/2009',20,'a01','usd'
union all select '05/05/2010',30,'a02','hkd'
union all select '08/22/2010',40,'a03','rmb'
union all select '08/22/2010',40,'a03','usd'
declare @tb table(years int,month int,cru nvarchar(10),rates decimal(10,5))
insert into @tb select 2009,03,'usd',6.5674
union all select 2010,05,'hkd',0.8676
union all select 2010,08,'usd',6.8992
select a.*,b.rates from @ta a
join @tb b on year(a.prcdate)=b.years and month(a.prcdate)=b.month
/*prcdate amount reportno cru rates
----------------------- ----------- ---------- ---------- ---------------------------------------
2009-03-22 00:00:00.000 20 a01 usd 6.56740
2010-05-05 00:00:00.000 30 a02 hkd 0.86760
2010-08-22 00:00:00.000 40 a03 rmb 6.89920
2010-08-22 00:00:00.000 40 a03 usd 6.89920(4 行受影响)
*/