有两个表,表a保存每天的资金余额之类的数据,资金余额分为人民币,港币和美元三个币种。 表b保存每天的汇率,现在如何实现当汇率表中若缺少某一交易日的汇率时,就以6.8853和0.8864分别作为美元和港币汇率的缺省值进行换算。用left join好象效率上不是很好吧。SELECT a.bizdate, a.orgid, a.custmgrid, a.fundid,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) asset_0,
max(case a.moneytype when 1 then a.fundeffect + a.fundbal else 0 end) asset_1,
max(case a.moneytype when 2 then a.fundeffect + a.fundbal else 0 end) asset_2,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) + max(case a.moneytype when 1 then (a.fundeffect + a.fundbal) * b.usd else 0 end) + max(case a.moneytype when 2 then (a.fundeffect + a.fundbal) * b.hkd else 0 end) asset
FROM a a,
a b
WHERE a.bizdate = b.bizdate
GROUP BY a.bizdate, a.orgid, a.custmgrid, a.fundid
我写错了,应该是 b bSELECT a.bizdate, a.orgid, a.custmgrid, a.fundid,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) asset_0,
max(case a.moneytype when 1 then a.fundeffect + a.fundbal else 0 end) asset_1,
max(case a.moneytype when 2 then a.fundeffect + a.fundbal else 0 end) asset_2,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) + max(case a.moneytype when 1 then (a.fundeffect + a.fundbal) * b.usd else 0 end) + max(case a.moneytype when 2 then (a.fundeffect + a.fundbal) * b.hkd else 0 end) asset
FROM a a, b b
WHERE a.bizdate = b.bizdate
GROUP BY a.bizdate, a.orgid, a.custmgrid, a.fundid
SELECT a.bizdate, a.orgid, a.custmgrid, a.fundid,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) asset_0,
max(case a.moneytype when 1 then a.fundeffect + a.fundbal else 0 end) asset_1,
max(case a.moneytype when 2 then a.fundeffect + a.fundbal else 0 end) asset_2,
max(case a.moneytype when 0 then a.fundeffect + a.fundbal else 0 end) + max(case a.moneytype when 1 then (a.fundeffect + a.fundbal) * isnull(b.usd,6.8853) else 0 end) + max(case a.moneytype when 2 then (a.fundeffect + a.fundbal) * isnull(b.hkd,0.8865) else 0 end) asset
FROM #a a LEFT JOIN
a b
on a.bizdate = b.bizdate
GROUP BY a.bizdate, a.orgid, a.custmgrid, a.fundid
if object_id('a')<>0
drop table a
go
create table a([day] datetime,montype int,[money] int)insert into a
select '2008-1-1',0,34 union all
select '2008-1-1',1,12 union all
select '2008-1-1',2,32 union all
select '2008-1-2',0,23 union all
select '2008-1-3',2,34select * from aif object_id('b')<>0 drop table b
go
create table b([day] datetime,usd decimal(8,3),hkd decimal(8,3))
insert into b
select '2008-1-1',6.853,0.854 union all
select '2008-1-3',6.843,0.864 select * from bselect a.day,a.montype,money,usd=isnull(b.usd,6.8853),hkd=isnull(b.hkd,0.8864) from a left join b on a.day=b.day2008-01-01 00:00:00.000 0 34 6.853 .854
2008-01-01 00:00:00.000 1 12 6.853 .854
2008-01-01 00:00:00.000 2 32 6.853 .854
2008-01-02 00:00:00.000 0 23 6.885 .886 --缺省的
2008-01-03 00:00:00.000 2 34 6.843 .864